Advertisement Space

When the optimizer makes the wrong choice

Oracle's Cost-Based Optimizer is remarkably good, but occasionally it makes suboptimal choices — usually due to stale statistics, data skew, or complex multi-table joins. Hints let you override its decisions.

Recipe #1 - Full table scan hint

Force a full table scan when you know you're reading most of the table:

SELECT /*+ FULL(e) */ ename, sal
FROM emp e
WHERE sal > 1000;

Recipe #2 - Index hints

Force the use of a specific index:

SELECT /*+ INDEX(e emp_deptno_idx) */ ename, sal
FROM emp e
WHERE deptno = 10;

Prevent index usage (force full scan):

SELECT /*+ NO_INDEX(e emp_deptno_idx) */ ename, sal
FROM emp e
WHERE deptno = 10;

Recipe #3 - Join order and method hints

Control how tables are joined:

-- Force nested loops join
SELECT /*+ USE_NL(e d) */ e.ename, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno;

-- Force hash join
SELECT /*+ USE_HASH(e d) */ e.ename, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno;

-- Force specific join order
SELECT /*+ LEADING(d e) */ e.ename, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno;

Recipe #4 - Parallel execution hints

Speed up large queries by using multiple CPU cores:

SELECT /*+ PARALLEL(e, 4) */ deptno, AVG(sal)
FROM emp e
GROUP BY deptno;

For DML operations:

INSERT /*+ APPEND PARALLEL(t, 4) */ INTO target_table t
SELECT /*+ PARALLEL(s, 4) */ * FROM source_table s;

Recipe #5 - FIRST_ROWS for interactive applications

Optimize for returning the first few rows quickly (useful for paginated UIs):

SELECT /*+ FIRST_ROWS(20) */ empno, ename, sal
FROM emp
ORDER BY sal DESC;

Important warnings