Hints:
- Hints always force the use of the cost based optimizer (Except RULE).
- Use ALIASES for the tablenames in the hints.
- Ensure tables are analyzed. - Syntax: /*+ HINT HINT ... */ (In PLSQL the space between the '+' AND
the first letter of the hint is vital
so /*+ ALL_ROWS */ is fine
but /*+ALL_ROWS */ will cause problems
) - Optimizer Mode:
FIRST_ROWS, ALL_ROWS Force CBO first rows or all rows.
RULE Force Rule if possible ORDERED Access tables in the order of the FROM clause ORDERED_PREDICATES Use in the WHERE clause to apply predicates
in the order that they appear.
Does NOT apply predicate evaluation ON INDEX keys - Sub-Queries/views:
PUSH_SUBQ Causes all subqueries in a query block to be
executed at the earliest possible time.
Normally subqueries are executed AS the last
is applied is outerjoined or remote or joined
with a merge JOIN. (>=7.2) NO_MERGE(v) Use this hint in a VIEW to PREVENT it
being merged INTO the parent query. (>=7.2)
or use NO_MERGE(v) in parent query block
to prevent VIEW V being merged
MERGE(v) Do merge VIEW V MERGE_AJ(v) } Put hint in a NOT IN subquery to perform (>=7.3)
HASH_AJ(v) } SMJ anti-JOIN or hash anti-JOIN. (>=7.3)
Eg: SELECT .. WHERE deptno is NOT null
AND deptno NOT IN
(SELECT /*+ HASH_AJ */ deptno ...)
HASH_SJ(v) } Transform EXISTS subquery INTO HASH or MERGE
MERGE_SJ(v) } semi-JOIN to access "v" PUSH_JOIN_PRED(v) Push JOIN predicates INTO VIEW V
NO_PUSH_JOIN_PRED(v) Do NOT push JOIN predicates - Access:
FULL(tab) Use FTS ON tab
CACHE(tab) If TABLE within <Parameter:CACHE_SIZE_THRESHOLD>
treat AS if it had the CACHE option SET.
See <Parameter:CACHE_SIZE_THRESHOLD>. Only
applies if FTS used.
NOCACHE(tab) Do NOT cache TABLE even if it has CACHE option
SET. Only relevant for FTS.
ROWID(tab) Access tab by ROWID directly
SELECT /*+ ROWID( TABLE ) */ ...
FROM tab WHERE ROWID between '&1' AND '&2'; CLUSTER(tab) Use CLUSTER scan to access 'tab'
HASH(tab) Use hash scan to access 'tab'
INDEX( tab INDEX ) Use 'INDEX' to access 'tab'
INDEX_ASC( tab INDEX ) Use 'INDEX' to access 'tab' for range scan.
INDEX_DESC( tab INDEX ) Use descending INDEX range scan
(JOIN problems pre 7.3)
INDEX_FFS( tab INDEX) INDEX fast full scan - rather than FTS. INDEX_COMBINE( tab i1.. i5 )
Try to use some boolean combination of
bitmap INDEX/s i1,i2 etc AND_EQUAL(tab i1.. i5 ) Merge scans of 2 to 5 single COLUMN indexes. USE_CONCAT Use concatenation (Union All) for OR (or IN)
statements. (>=7.2). See [NOTE:17214.1]
(7.2 requires <Event:10078>, 7.3 no hint req) NO_EXPAND Do NOT perform OR-expansion (Ie: Do NOT use
Concatenation). DRIVING_SITE(TABLE) Forces query execution to be done at the
site WHERE "table" resides
- Joining:
USE_NL(tab) Use TABLE 'tab' AS the driving TABLE in a
Nested Loops JOIN. If the driving row source
is a combination of tables name one of the
tables in the inner JOIN AND the NL should
drive off the entire row-source.
Does NOT work unless accompanied by an ORDERED
hint. USE_MERGE(tab..) Use 'tab' AS the driving TABLE in a sort-merge
JOIN.
Does NOT work unless accompanied by an ORDERED
hint. USE_HASH(tab1 tab2) JOIN each specified TABLE with another row
source with a hash JOIN. 'tab1' is joined to
previous row source using a hash JOIN. (>=7.3) STAR Force a star query plan if possible. A star
plan has the largest TABLE in the query last
in the JOIN order AND joins it with a nested
loops JOIN ON a concatenated INDEX. The STAR
hint applies when there are at least 3 tables
AND the large TABLE's concatenated INDEX has
at least 3 columns AND there are no conflicting
access or JOIN method hints. (>=7.3) STAR_TRANSFORMATION Use best plan containing a STAR transformation
(if there is one) - Parallel Query Option:
PARALLEL ( TABLE, <degree> [, <instances>] )
Use parallel degree / instances AS specified PARALLEL_INDEX(TABLE, [ INDEX, [ degree [,instances] ] ] )
Parallel range scan for partitioned INDEX PQ_DISTRIBUTE(tab,out,in) How to distribute rows FROM tab in a PQ
(out/in may be HASH/NONE/BROADCAST/PARTITION) NOPARALLEL(TABLE) No parallel ON "table"
NOPARALLEL_INDEX(TABLE [,INDEX]) - Miscellaneous
APPEND Only valid for INSERT .. SELECT.
Allows INSERT to work LIKE direct load
or to perform parallel INSERT. See [NOTE:50592.1]
NOAPPEND Do NOT use INSERT APPEND functionality REWRITE(v1[,v2]) 8.1+ With a VIEW list use eligible materialized VIEW
Without VIEW list use ANY eligible MV
NOREWRITE 8.1+ Do NOT rewrite the query
- Hints always force the use of the cost based optimizer (Except RULE).
- Use ALIASES for the tablenames in the hints.
- Ensure tables are analyzed. - Syntax: /*+ HINT HINT ... */ (In PLSQL the space between the '+' AND
the first letter of the hint is vital
so /*+ ALL_ROWS */ is fine
but /*+ALL_ROWS */ will cause problems
) - Optimizer Mode:
FIRST_ROWS, ALL_ROWS Force CBO first rows or all rows.
RULE Force Rule if possible ORDERED Access tables in the order of the FROM clause ORDERED_PREDICATES Use in the WHERE clause to apply predicates
in the order that they appear.
Does NOT apply predicate evaluation ON INDEX keys - Sub-Queries/views:
PUSH_SUBQ Causes all subqueries in a query block to be
executed at the earliest possible time.
Normally subqueries are executed AS the last
is applied is outerjoined or remote or joined
with a merge JOIN. (>=7.2) NO_MERGE(v) Use this hint in a VIEW to PREVENT it
being merged INTO the parent query. (>=7.2)
or use NO_MERGE(v) in parent query block
to prevent VIEW V being merged
MERGE(v) Do merge VIEW V MERGE_AJ(v) } Put hint in a NOT IN subquery to perform (>=7.3)
HASH_AJ(v) } SMJ anti-JOIN or hash anti-JOIN. (>=7.3)
Eg: SELECT .. WHERE deptno is NOT null
AND deptno NOT IN
(SELECT /*+ HASH_AJ */ deptno ...)
HASH_SJ(v) } Transform EXISTS subquery INTO HASH or MERGE
MERGE_SJ(v) } semi-JOIN to access "v" PUSH_JOIN_PRED(v) Push JOIN predicates INTO VIEW V
NO_PUSH_JOIN_PRED(v) Do NOT push JOIN predicates - Access:
FULL(tab) Use FTS ON tab
CACHE(tab) If TABLE within <Parameter:CACHE_SIZE_THRESHOLD>
treat AS if it had the CACHE option SET.
See <Parameter:CACHE_SIZE_THRESHOLD>. Only
applies if FTS used.
NOCACHE(tab) Do NOT cache TABLE even if it has CACHE option
SET. Only relevant for FTS.
ROWID(tab) Access tab by ROWID directly
SELECT /*+ ROWID( TABLE ) */ ...
FROM tab WHERE ROWID between '&1' AND '&2'; CLUSTER(tab) Use CLUSTER scan to access 'tab'
HASH(tab) Use hash scan to access 'tab'
INDEX( tab INDEX ) Use 'INDEX' to access 'tab'
INDEX_ASC( tab INDEX ) Use 'INDEX' to access 'tab' for range scan.
INDEX_DESC( tab INDEX ) Use descending INDEX range scan
(JOIN problems pre 7.3)
INDEX_FFS( tab INDEX) INDEX fast full scan - rather than FTS. INDEX_COMBINE( tab i1.. i5 )
Try to use some boolean combination of
bitmap INDEX/s i1,i2 etc AND_EQUAL(tab i1.. i5 ) Merge scans of 2 to 5 single COLUMN indexes. USE_CONCAT Use concatenation (Union All) for OR (or IN)
statements. (>=7.2). See [NOTE:17214.1]
(7.2 requires <Event:10078>, 7.3 no hint req) NO_EXPAND Do NOT perform OR-expansion (Ie: Do NOT use
Concatenation). DRIVING_SITE(TABLE) Forces query execution to be done at the
site WHERE "table" resides
- Joining:
USE_NL(tab) Use TABLE 'tab' AS the driving TABLE in a
Nested Loops JOIN. If the driving row source
is a combination of tables name one of the
tables in the inner JOIN AND the NL should
drive off the entire row-source.
Does NOT work unless accompanied by an ORDERED
hint. USE_MERGE(tab..) Use 'tab' AS the driving TABLE in a sort-merge
JOIN.
Does NOT work unless accompanied by an ORDERED
hint. USE_HASH(tab1 tab2) JOIN each specified TABLE with another row
source with a hash JOIN. 'tab1' is joined to
previous row source using a hash JOIN. (>=7.3) STAR Force a star query plan if possible. A star
plan has the largest TABLE in the query last
in the JOIN order AND joins it with a nested
loops JOIN ON a concatenated INDEX. The STAR
hint applies when there are at least 3 tables
AND the large TABLE's concatenated INDEX has
at least 3 columns AND there are no conflicting
access or JOIN method hints. (>=7.3) STAR_TRANSFORMATION Use best plan containing a STAR transformation
(if there is one) - Parallel Query Option:
PARALLEL ( TABLE, <degree> [, <instances>] )
Use parallel degree / instances AS specified PARALLEL_INDEX(TABLE, [ INDEX, [ degree [,instances] ] ] )
Parallel range scan for partitioned INDEX PQ_DISTRIBUTE(tab,out,in) How to distribute rows FROM tab in a PQ
(out/in may be HASH/NONE/BROADCAST/PARTITION) NOPARALLEL(TABLE) No parallel ON "table"
NOPARALLEL_INDEX(TABLE [,INDEX]) - Miscellaneous
APPEND Only valid for INSERT .. SELECT.
Allows INSERT to work LIKE direct load
or to perform parallel INSERT. See [NOTE:50592.1]
NOAPPEND Do NOT use INSERT APPEND functionality REWRITE(v1[,v2]) 8.1+ With a VIEW list use eligible materialized VIEW
Without VIEW list use ANY eligible MV
NOREWRITE 8.1+ Do NOT rewrite the query
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货