How to Slash SQL I/O and CPU Costs: Practical Query Performance Tips

1. Reduce Scope

Minimize extra I/O, CPU, physical and logical overhead by narrowing the data range each statement touches.

Narrow scan range : Add filter conditions—preferably indexed—to full scans.

Partition pruning : Apply partition predicates whenever possible.

Avoid unnecessary join conditions : For example, A.id in (Select id from a1) can be simplified by moving the predicate into the subquery or removing the join entirely.

Replace LEFT JOIN with INNER JOIN when preserving unmatched rows is not required, which reduces intermediate results and logical reads.

2. Execution Plan Tips

Understanding and influencing the optimizer’s execution plan is key to performance.

Beware of mismatched table/partition sizes that can cause incorrect plans.

Use hints (e.g., /*+ DRIVING_SITE */, FULL, NO_INDEX, USE_HASH) only after consulting the DBA.

Prefer bind variables to improve plan stability and reuse.

Use scalar subqueries judiciously; they can increase cost if overused.

Pass constant values directly in predicates so the optimizer can choose the best plan (e.g., place primary‑key conditions in the ON clause of a MERGE).

Watch for costly operators:

CARTESIAN (cross product) – ensure every table is properly joined.

FILTER on a large driving table – can become a performance hotspot.

NESTED LOOP with full scans on both sides – often the worst case.

If both tables require full scans, consider adding or removing indexes after confirming actual usage statistics.

3. Practical Experience Points

MERGE vs UPDATE : Place filter conditions that involve indexed columns in the ON clause of MERGE to use primary‑key indexes; UPDATE is usually faster for single‑table modifications.

Avoid predicates like (id = 1 OR id IN (...)) that force FILTER operators.

Prefer EXISTS over IN to reduce FILTER usage; if IN causes full scans, rewrite as a JOIN.

For pagination, use ROWNUM = 1 in Oracle or LIMIT 1 in MySQL to fetch a single row efficiently.

Leverage ROWID where appropriate for fast row access.

Do not use IS NULL on indexed columns; instead use function‑based indexes such as NVL(col,0)=0.

When only existence matters, replace SELECT COUNT(*) with SELECT 1 FROM ... WHERE ROWNUM = 1 to avoid scanning all rows.

Temporary tables are not automatically gathered by statistics; create‑as‑select tables behave like regular tables but require explicit statistics collection after massive inserts.

References: blog.itpub.net/28602568 (multiple viewspace links).