Oracle Database Administration: The Essential Refe

Additional hints

These miscellaneous hints can also be used to force the optimizer to function in certain ways. The following list describes each hint in this category and provides an example of its use:

CACHE

Forces blocks that are retrieved as part of a full table scan to be placed at the front of the least recently used (LRU) queue, thus helping ensure they stay in memory. Normally, data blocks that are read as part of a full table scan are not moved to the front of the queue.

SELECT /*+ FULL(emp) CACHE (emp) */  ename
FROM scott.emp;

NOCACHE

Forces blocks that are retrieved as part of a full table scan not to be placed at the front of the LRU queue. This is the normal process for full table scans.

SELECT /*+ FULL(emp) NOCACHE(emp) */ ename
FROM scott.emp;

MERGE

Forces the INIT.ORA parameter COMPLEX_VIEW_MERGING to be evaluated to TRUE for this query. This allows the optimizer to decompose a view or subquery and evaluate it with all other WHERE conditions. In the following example, emp_view is a view.

SELECT /*+ MERGE(v) */  v.ename, d.dname
FROM emp_view v, dept d
WHERE v.deptno = d.deptno;

NOMERGE

Forces the INIT.ORA parameter COMPLEX_VIEW_MERGING to be evaluated to FALSE for this query. This forces the optimizer to evaluate the view prior to joining the results with the WHERE conditions. In the following example, emp_view is a view.

SELECT /*+ NOMERGE(v) */  v.ename, d.dname
FROM emp_view v, dept d
WHERE v.deptno = d.deptno;

PUSH_ JOIN_PRED

Forces the INIT.ORA parameter PUSH_ JOIN_PREDICATE ...