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;

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;

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;

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;

Forces the INIT.ORA parameter PUSH_ JOIN_PREDICATE to be evaluated to TRUE for this query. This allows the optimizer to push join predicates into views within the query for potentially better performance. In the following example, emp_view is a view.

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

Forces the INIT.ORA parameter PUSH_ JOIN_PREDICATE to be evaluated to FALSE for this query. This forces the optimizer to evaluate the specified view without pushing join predicates into the view. In the following example, emp_view is a view.

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

Allows the optimizer to consider evaluating views that have not been merged early in the execution plan. Normally, nonmerged views are evaluated at the end. If the nonmerged view will generate a significant reduction in rows, it may be beneficial to use this hint. In the following example, emp_view is a view.

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

Forces the optimizer to choose the best transformation access path of those generated, even if a nontransformation access path has a lower cost. Normally, the optimizer will choose the lowest cost access path. Note that if the optimizer is unable to generate the required subqueries in order to create a star transformation, it will ignore this hint.

SELECT /*+ STAR_TRANSFORMATION */  id, customer_name
FROM order_view
WHERE settle_currency='UK Pounds'
AND quoted_currency='US Dollars'
AND sales_office_id='North America'