Oracle Database Administration: The Essential Refe

Goal hints

Hints in this category allow you to specify a goal for the query. These are the values that can be specified at the database level with the INIT.ORA parameter OPTIMIZER_MODE or at the session level with the ALTER SESSION SET OPTIMIZER_MODE command. The following list describes each hint in this category and provides an example of its use:

ALL_ROWS

Tells the optimizer to choose the access path that provides the best throughput by minimizing total system resources.

SELECT /*+ ALL_ROWS */  ename,dname
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

FIRST_ROWS

Tells the optimizer to choose the access path that minimizes initial response time.

SELECT /*+ FIRST_ROWS */  ename,dname
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

CHOOSE

Tells the optimizer to use cost-based optimization if at least one of the tables in the join has been analyzed. Otherwise, revert to RULE.

SELECT /*+ CHOOSE */  ename,dname
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

RULE

Tells the optimizer to revert to using the rule-based optimizer rules.

SELECT /*+ RULE */  ename,dname
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

Warning

If the optimizer uses cost-based optimization and one or more of the tables have not been analyzed, Oracle will automatically perform an ANALYZE TABLE ... ESTIMATE STATISTICS with the default 1064 rows, use the results, and then not store them. If this table is used often, Oracle will constantly reanalyze the table.