Correlated Subqueries
Another feature requiring the cost-based optimizer is
hints. There are many hints. A hint overrides any session setting and
instance setting in the parameter file. All hints but one, RULE, utilize the
cost-based optimizer. Hints are treated as special comments. If you make a
syntax error in a hint, Oracle does not return an error message. Instead,
your hint is ignored. The hint must be the first part of a SELECT, UPDATE, or
DELETE statement. You can combine more than one hint per SQL statement as
long as they do not conflict. The following example uses the ORDERED hint.
The ORDERED hint tells the cost-based optimizer the join chain. The join
chain consists of the tables in the FROM clause from the left to right as you
read the FROM clause. The table to the far left in the FROM clause is the
first table in the join chain. The first table in the join chain is called
the DRIVING table or the OUTER table. The following example uses the ORDERED
hint. By reading the output of AUTOTRACE top down, the FIRST table you see is
the EMP table. The first table down from the top is the DRIVING table.
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT /*+ORDERED */ ENAME, LOC
2
FROM EMP, DEPT -- EMP DRIVES
3
WHERE EMP.DEPTNO = DEPT.DEPTNO;
Execution Plan
-----------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE
(Cost=3 Card=14 Bytes=224)
1 0 HASH
JOIN
(Cost=3 Card=14 Bytes=224)
2 1
TABLE ACCESS (FULL) OF 'EMP'
(Cost=1 Card=14 Bytes=98)
3 1
TABLE ACCESS (FULL) OF 'DEPT'
(Cost=1 Card=4 Bytes=36)
You can write your hints using one of two formats. The
example above uses the C language construct of a comment;" /* comment */".
You can also use double dashes "- –." However, using double dashes does not
permit you to place any column names on the same line as your hint, as the
following code indicates. Notice that the driving table is DEPT because it is
to the far left in the FROM clause.
SQL> SELECT --+ORDERED
2
ENAME, LOC
3
FROM
DEPT, EMP -- DEPT Drives
4*
WHERE
EMP.DEPTNO = DEPT.DEPTNO;
Execution Plan
-----------------------------------------
0
SELECT
STATEMENT Optimizer=CHOOSE
(Cost=3 Card=14 Bytes=224)
1 0 HASH
JOIN
(Cost=3 Card=14 Bytes=224)
2 1
TABLE ACCESS (FULL) OF 'DEPT'
(Cost=1 Card=4 Bytes=36)
3 1
TABLE ACCESS (FULL) OF 'EMP'
(Cost=1 Card=14 Bytes=98)
There are hints to invoke each
optimizer: use indexes, use full table scans, invoke a join method for
joining tables, the join chain, working with views, working with subqueries,
parallel queries, and star queries. Oracle continues to add new hints with
each release of Oracle. There are some undocumented hints as well. You can
view hints that Oracle writes to itself, recursive calls, in the dynamic
performance view v$sqlareaor v$sql.
SQL> SELECT
SUBSTR(SQL_TEXT,1,40) CODE
2
FROM
V$SQLAREA
3
WHERE
SQL_TEXT LIKE '%--+%' OR
4
SQL_TEXT LIKE '%/*+%'
5*
ORDER BY SQL_TEXT;
CODE
----------------------------------------
SELECT --+ORDERED
ENAME, LOC
FROM
SELECT /*+ORDERED */
ENAME, LOC
FROM
SELECT SUBSTR(SQL_TEXT,1,40) CODE FROM
select /*+ index(idl_char$ i_idl_char1)
select /*+ index(idl_sb4$ i_idl_sb41) +*
select /*+ index(idl_sb4$ i_idl_sb41) +*
select /*+ index(idl_ub1$ i_idl_ub11) +*
select /*+ index(idl_ub2$ i_idl_ub21) +*
select /*+ rule */ bucket_cnt, row_cnt,
This list is not exhaustive, but it does include most of
the hints available.
ALL_ROWS
AND_EQUAL(STATS I1
I2 I3 I4 I5)
APPEND
CACHE(STATS)
CHOOSE
CLUSTER(STATS)
DRIVING_SITE(STATS)
FIRST_ROWS
FULL(STATS)
HASH(STATS)
HASH_AJ(STATS)
HASH_SJ(STATS)
INDEX(STATS
I_STATS_REGION)
INDEX_ASC(STATS
I_STATS_REGION)
INDEX_COMBINE(STATS IBM_STATS_SEX)
INDEX_COMBINE(STATS)
INDEX_DESC(STATS
I_STATS_REGION)
INDEX_FFS(STATS
I_STATS_REGION)
INDEX_JOIN
LEADING(STATS)
MERGE(VIEW_NAME)
MERGE_AJ
MERGE_SJ
NOAPPEND
NOCACHE(STATS)
NOPARALLEL
NOPARALLEL_INDEX(STATS,I_STATS_REGION)
NOREWRITE
NO_EXPAND
NO_FACT(STATS)
NO_INDEX(STATS
I_STATS_REGION)
NO_MERGE(VIEW_NAME)
NO_PUSH_JOIN_PRED(STATS)
ORDERED
ORDERED_PREDICATES
PARALLEL(STATS,4)
PARALLEL_INDEX(STATS,I_STATS_REGION,4,2)
PQ_DISTRIBUTE(INNER_TABLE,OUT_DIST,
INNER_ DIST)
PUSH_JOIN_PRED(STATS)
PUSH_SUBQ
REWRITE
ROWID(STATS)
RULE
STAR
STAR_TRANSFORMATION
USE_CONCAT
USE_HASH(INNER_TABLE)
USE_MERGE(INNER_TABLE)
USE_MERGE(INNER_TABLE) ORDERED
FULL(STATS)
USE_NL(INNER_TABLE) ORDERED
USE_NL(STATS
STATS_HIST)
USE_NL(STATS)
If you use a table alias in the SQL statement, you must use
the same alias in your hint. Otherwise, Oracle ignores your hint. The table
alias is not
case sensitive. The following hint is ignored by Oracle because a
table alias of "s" is used, but not referenced in the hint.
SQL> SELECT --+FULL(STATS)
2
*
3
FROM STATS s
4*
WHERE REGION = 'NW';
Execution Plan
-----------------------------------------
0
SELECT
STATEMENT Optimizer=CHOOSE
(Cost=4 Card=1 Bytes=21)
1 0
TABLE ACCESS (BY INDEX ROWID) OF
'STATS' (Cost=4 Card=1 Bytes=21)
2 1
INDEX (RANGE SCAN) OF
'I_STATS_REGION' (NONUNIQUE)
(Cost=3 Card=1)
The next example uses a table alias of uppercase "S" to
verify that the table alias is not case sensitive. The EXPLAIN PLAN output
from AUTOTRACE indicates that the table alias is not case sensitive.
SQL> SELECT /*+FULL(S) */ *
2
FROM STATS s
3
WHERE REGION = 'NW';
Execution Plan
-----------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=583 Card=1 Bytes=21)
1 0 TABLE ACCESS (FULL) OF 'STATS'
(Cost=583 Card=1 Bytes=21)
and include the URL for the page.
