Mastering Oracle Execution Plans: Control, Hints, and Design Tricks

Overview of Controlling Execution Plans

Controlling an Oracle execution plan can solve two practical problems: (1) avoid the overhead of gathering fresh statistics during peak hours, and (2) work around bugs that cause the optimizer to choose a wrong plan. By forcing a plan, you can keep the database stable while you investigate root causes.

Understanding Hints

Hints are special comments that tell the optimizer to follow a specific strategy. They can force join methods, index usage, or the order of tables. The article lists many hint types (see the hint‑type diagram) and shows the basic syntax, e.g. SELECT /*+ INDEX(t, idx_emp_deptno) */ * FROM emp t;.

Common Reasons Hints Fail

Algorithm not supported – a USE_HASH hint cannot be applied to an inequality join ( t1.id > t2.t1_id), so the optimizer falls back to a nested‑loops join.

Conflicting hints – combining USE_NL(t2) with LEADING(t2) creates a contradiction (driving vs. preceding table) and the hint is ignored.

Result differs from expectation – an INDEX hint on a column that allows NULL values may produce wrong row counts because the index does not store NULLs.

Syntax errors – using the original table name instead of its alias, misspelling INDEX as INDEXX, or malformed comment delimiters ( /*+full(t)*/ vs /*full(t)*/) cause the hint to be discarded.

Non‑Hint Ways to Influence Plans

SQL rewrite – using a WITH clause, INSERT ALL, or ROWNUM‑based pagination can change the optimizer’s cost model.

Design features – partition pruning, cluster tables, index‑organized tables (IOT), materialized views, parallelism hints, and rowid usage all affect the generated plan.

Statistics manipulation – SET_TABLE_STATS can fake table size, and virtual indexes let you test an index’s impact without creating it.

Practical Scripts

Environment preparation (DDL):

DROP TABLE emp PURGE;
CREATE TABLE emp AS SELECT * FROM scott.emp;
CREATE INDEX idx_emp_deptno ON emp(deptno);
CREATE INDEX idx_emp_empno ON emp(empno);
DROP TABLE dept PURGE;
CREATE TABLE dept AS SELECT * FROM scott.dept;
CREATE INDEX idx_dept_deptno ON dept(deptno);

Sample hint usage:

SELECT /*+ INDEX(t, idx_emp_deptno) */ * FROM emp t WHERE deptno = 10;
SELECT /*+ USE_NL(t2) LEADING(t2) */ * FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT /*+ INDEX(t, idx_test_objid) */ * FROM test t WHERE object_id > 0;

Fixing Specific Issues

Making a nullable column non‑null changes a full‑table scan to an INDEX FAST FULL SCAN:

SELECT /*+ INDEX(t, idx_object_id) */ * FROM test t WHERE object_id > 0;

Adding primary/foreign keys can eliminate unnecessary joins; in one example the optimizer accesses only the child table because the foreign‑key relationship guarantees the join result.

Stabilizing Plans with Outlines

After generating a desirable plan, you can capture it with an outline (or SQL plan baseline) so that future executions reuse the same plan even if statistics change:

DECLARE
  l_plan_name VARCHAR2(30) := 'MY_OUTLINE';
BEGIN
  DBMS_OUTLINE.CREATE_OUTLINE(
    name => l_plan_name,
    sql_text => 'SELECT ...',
    outline => DBMS_OUTLINE.GET_OUTLINE('SELECT ...')
  );
END;

Once the outline exists, the optimizer will follow it unless you explicitly drop it.

By combining hints, SQL rewrites, design‑level features, and controlled statistics, you can reliably steer Oracle’s optimizer to produce fast, predictable execution plans without the need for costly statistics collection during production windows.