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:
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;
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;
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;
Tells the optimizer to revert to using the rule-based optimizer rules.
SELECT /*+ RULE */ ename,dname FROM emp, dept WHERE emp.deptno = dept.deptno;
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.