Practical Tips for Optimizing Oracle SQL Execution Plans

Scope Reduction

Apply selective filter conditions, preferably indexed, to avoid full table scans.

When using partitioned tables, always include partition predicates.

Identify and remove unnecessary join conditions, e.g., replace A.id in (select id from a1 where id=1) with a direct filter.

Convert LEFT JOIN to inner JOIN when possible to eliminate extra intermediate results.

Execution Plan Considerations

Avoid mis‑estimations caused by treating large partitions as small ones.

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

Leverage bind variables to promote plan reuse.

Prefer scalar subqueries judiciously; they can increase cost.

Pass constant values directly in predicates to help the optimizer choose better plans.

Common Pitfalls in Execution Plans

CARTESIAN (cross join) indicates missing join predicates.

FILTER operations on large driving tables can cause performance bottlenecks.

NESTED LOOPS with full scans on both tables lead to severe scaling issues.

When both tables are fully scanned, consider adding appropriate indexes or redesigning the join strategy.

Specific SQL Tuning Tips

Place filter conditions that involve indexed columns in the ON clause of a MERGE statement to ensure index usage.

Prefer UPDATE for simple single‑table modifications; MERGE can be more efficient when updating and filtering the same table simultaneously.

Avoid constructs like (id = 1 OR id IN (subquery)) that trigger filter operations.

Prefer EXISTS over IN to reduce the chance of filter steps; if IN causes a full scan, rewrite using a join.

For pagination, choose between ROWNUM (Oracle) and ROW_NUMBER() (SQL Server/MySQL) based on the database and query pattern.

Use ROWID when appropriate for fast row access.

Replace IS NULL checks with function‑based indexes, e.g., NVL(column,0)=0, to keep index usage.

When counting rows, use ROWNUM = 1 to fetch a single row instead of retrieving the entire result set.

Consider temporary tables for large data‑download operations, but be aware that temporary tables are not automatically gathered statistics; use CREATE TABLE AS SELECT without statistics and rely on dynamic sampling, or gather stats manually after bulk inserts.

These recommendations are distilled from practical experience and aim to help developers and DBAs produce more efficient Oracle execution plans.