Previous Table of Contents Next


The optimizer goal hint controls one of three modes: RULE will force the rule-based optimizer, FIRST_ROWS (best response), and ALL_ROWS (best throughput) will use the cost-based optimizer and force the optimizer to the desired goal. Most of the access-method-controlling hints are listed in Table 24.2.

Table 24.2. Access method hints.
Hint Description
AND_EQUAL Use the AND_EQUAL hint when more than one equality criterion exists on a single table.
select/*+ AND_EQUAL(emp,PK_EMP,EMP_NAME) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
and ename = ‘SMITH’
CACHE Use the CACHE hint to place the entire table in the buffer cache. The table is placed at the most recently used end of the buffer cache. This hint is good for small tables that are accessed often.
select/*+ CACHE(emp) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
CLUSTER Use the CLUSTER hint to access a table in a cluster without the use of an index.
select/*+ CLUSTER(emp) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
FULL Use the FULL hint to perform a full table scan on a table.
select/*+ FULL(emp) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
HASH Use the HASH hint to access a table in a hashed cluster without the use of an index.
select/*+ HASH */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
INDEX Use an INDEX hint to instruct Oracle to use one of the indexes specified as a parameter.
select/*+ INDEX(emp,PK_EMP) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
INDEX_COMBINE The INDEX_COMBINE forces the use of bitmap indexes.
select/*+ INDEX_COMBINE(emp,ENAME) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
NOCACHE Use the NOCACHE hint to place the blocks of the table at the beginning of the buffer cache so as not to age any blocks out.
select/*+ NOCACHE(emp) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
NOPARALLEL Use the NOPARALLEL hint to not use multiple-server processes to service the operations on the specified table.
select/*+ NOPARALLEL(emp) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
ORDERED Use the ORDERED hint to access the tables in the FROM clause in the order that they appear.
select/*+ ORDERED(emp,dept) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
PARALLEL Use the PARALLEL hint to request multiple server processes to simultaneously service the operations on the specified table.
select/*+ PARALLEL(emp) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
PUSH_SUBQ The PUSH_SUBQ evaluates subqueries earlier, rather than as a filter operation.
select/*+ PUSH_SUBQ */ ename,dname
from emp, dept
where emp.deptno in
(
select deptno from dept)
ROWID Use the ROWID hint to access the table by ROWID.
select/*+ ROWID(emp) */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
STAR STAR hint invokes the STAR query feature.
select/*+ STAR */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
USE_CONCAT USE_CONCAT is used when a SQL statement has multiple criteria ORed together.
select/*+ USE_CONCAT */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
USE_HASH Use the USE_HASH hint to perform a hash join rather than a merge join or a nested loop join.
select/*+ USE_HASH */ ename,dname
from emp, dept
where emp.deptno = dept.deptno
USE_MERGE Use the USE_MERGE hint to perform a merge join rather than a nested loop join or a hash join.
select/*+ USE_MERGE */ ename,dname
from emp, dept
where emp.deptno = dept.deptno


Previous Table of Contents Next
Используются технологии uCoz