List and explain some of the hints for the CBO
SCOPE
Various
DETAILS
General
- "A statement block can have only one comment containing hints, and that comment must follow the
SELECT
,UPDATE
,INSERT
,MERGE
, orDELETE
keyword." . For documentation on hints see:Oracle Database 12c Release 2 / Administration
Database SQL Language Reference
Hints
http://docs.oracle.com - Syntax: /*+ HINT HINT ... */
Note: (In PLSQL the space between the '+' and the first letter of the hint is vital as otherwise the hint may be ignored.
So /*+ ALL_ROWS */ is fine but /*+ALL_ROWS */ will cause problems. See Bug:697121 - Hints always force the use of the cost based optimizer (Exception: the RULE hint).
- Hints must reference the table alias, if aliases are in use.
For Example:
Incorrect:SELECT /*+ FULL ( emp ) */ empno FROM emp myalias WHERE empno > 10;
Correct:SELECT /*+ FULL ( myalias ) */ empno FROM emp myalias WHERE empno > 10;
- Hints should not reference the schema name. For example:
SELECT /*+ index(schmaname.emp emp1) */ ...
should not be used. Alias the table name instead and use the alias in the hint.
- Invalid hints cause the hint to be ignored without warning
- Invalid hints may not be immediately obvious. For example: FIRST_ROWS as a hint when the statement has an ORDER BY clause
(since the data has to be ordered prior to the first row being returned the first_rows hint may not have the desired effect). - The access path to be HINT(ed) must be an available access path. For example an index hint referencing a non-existant index with fail silently.
- If third party tools do not contain the logic to support hints, then a potential workaround is to embed hints in a view and then reference that view.
- Refer to the Oracle Database Performance Tuning Guide for more on hints.
- There is a view from 11.1 on wards that shows obsolete hints:
SELECT NAME,INVERSE,SQL_FEATURE,VERSION FROM V$SQL_HINT ORDER BY NAME;
Hint Reference: -
Hints referring to 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 |
PUSH_JOIN_PRED(v) | Push join predicates into view V |
NO_PUSH_JOIN_PRED(v) | Do NOT push join predicates |
Access Hints
FULL(tab) | Use Full Table Scan FTS on table “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. |
CLUSTER(tab) | Use cluster scan to access 'tab' |
HASH(tab) | Use hash scan to access 'tab' |
INDEX(tab [ind]) | Use 'ind' scan to access 'tab' - Disables index_ffs. See Document 50607.1 How to specify an INDEX Hint |
NO_INDEX (tab [ind]) | Do not use 'ind' to access 'tab' |
INDEX_ASC(tab [ind]) | Use 'ind' to access 'tab' for range scan. |
INDEX_DESC(tab {ind]) | Use descending index range scan (Join problems pre 7.3) |
INDEX_FFS(tab [ind]) | Index fast full scan - rather than FTS. |
NO_INDEX_FFS(tab [ind]) | Exclude the option of using Index fast full scan - rather than FTS. |
INDEX_RRS(tab [ind]) | Index Rowid Range scan |
INDEX_COMBINE( tab i1.. i5 ) | Try to use some boolean combination of bitmap index/s i1,i2 etc |
INDEX_SS(tab [ind]) | Use 'ind' to access 'tab' with an index skip scan |
INDEX_SS_ASC(tab [ind]) | Use 'ind' to access 'tab' with an index skip scan in Ascending order |
INDEX_SS_DESC(tab [ind]) | Use 'ind' to access 'tab' with an index skip scan in Descending order |
NO_INDEX_SS(tab [ind]) | Exclude the option of using 'ind' to access 'tab' with an index skip scan |
USE_CONCAT | Use concatenation (Union All) for OR (or IN) statements. (>=7.2). See Document 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 inner table in a Nested Loops join. May not work unless by a hint that forces the correct join order (e.g. ORDERED hint). |
NO_USE_NL(tab) | Excludes the use of table 'tab' as the inner table in a Nested Loops join |
USE_NL_WITH_INDEX(tab) | The USE_NL_WITH_INDEX hint will cause the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table but only under the following condition: If no index is specified, the optimizer must be able to use some index with at least one join predicate as the index key. If an index is specified, the optimizer must be able to use that index with at least one join predicate as the index key. |
USE_MERGE(tab..) | Join each specified table with another row source using a sort-merge join. |
NO_USE_MERGE(tab..) | Excludes the option of joining each specified table with another row source using a sort-merge join. |
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) |
NO_USE_HASH(tab1 tab2) | Excludes the option of joining each specified table with another row source with a hash join. |
STAR_TRANSFORMATION | Use best plan containing a STAR transformation (if there is one) |
NO_STAR_TRANSFORMATION | Exclude the option of using the best plan containing a STAR transformation |
ORDERED | Access tables in the order of the FROM clause |
LEADING | This hint specifies only the driving table. From there CBO is free to investigate multiple join orders |
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 |
NO_PARALLEL(table) | Starting from 10g this syntax should be used |
NO_PARALLEL_INDEX(table [,index]) | Starting from 10g this syntax should be used |
Miscellaneous
APPEND | Only valid for INSERT .. SELECT. Allows INSERT to work like direct load or to perform parallel insert. |
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 |
NO_REWRITE | Starting from 10g this syntax should be used |
NO_UNNEST | Add to a subquery to prevent it from being unnested |
UNNEST | Unnests specified subquery block if possible |
SWAP_JOIN_INPUTS | Allows the user to switch the inputs of a join. See Document 171940.1 |
CARDINALITY(t1 [,..],n) | Makes the CBO to use different assumptions about cardinality at the table level |
Optimizer Mode
FIRST_ROWS | Force CBO first rows |
ALL_ROWS | Force CBO all rows |
RULE | Force RBO if possible |
Deprecated Hints
NOTE: Starting from Oracle version 10.1.0.2 the following hints have been deprecated and should not be used:
AND_EQUAL, HASH_AJ, MERGE_AJ, NL_AJ, HASH_SJ, MERGE_SJ, NL_SJ, ORDERED_PREDICATES, ROWID, STAR.
Descriptions for deprecated hints are included below for historical reference:
AND_EQUAL(tab i1.. i5 ) | Merge scans of 2 to 5 single column indexes. |
MERGE_AJ(v) | Put hint in a NOT IN subquery to perform sort-merge anti-join or hash anti-join or nested loops antijoin (>=7.3). |
ASH_AJ(v) | |
NL_AJ(v) | |
HASH_SJ(v) |
|
MERGE_SJ(v) | |
NL_SJ(v) | |
ORDERED_PREDICATES | Forces optimizer to apply predicates as they appear in the WHERE clause, except for predicates used as index keys |
ROWID(tab) | Access tab by ROWID directly. For example: |
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) |
NOPARALLEL(table) | No parallel on "table" |
NOPARALLEL_INDEX(table [,index]) | Opposite to PARALLEL_INDEX |
NOREWRITE | 8.1+ Do not rewrite the query |
PURPOSE
This document explains how to specify index hints successfully.
TROUBLESHOOTING STEPS
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
The format for an index hint is:
select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
There are a number of rules that need to be applied to this hint:
- The TABLE_NAME is mandatory in the hint
- The table alias MUST be used if the table is aliased in the query
- If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.
- The INDEX_NAME is optional.
- If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be applied.
- If a TABLE_NAME is supplied on its own then the optimizer will decide which index to use based on statistics.
- If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelled correctly then the hint will not be applied even though the TABLE_NAME is correct.
- If there are multiple index hints to be applied, then the simplest way of addressing this is to repeat the index hint syntax for each index e.g.:
SELECT /*+ index(TABLE_NAME1 INDEX_NAME1) index(TABLE_NAME2 INDEX_NAME2) */ col1...
- Remember that the parser/optimizer may have transformed/rewritten the query or may have chosen an access path which make the use of the index invalid and this may result in the index not being used.
Legacy Note: As long as the index() hint structure is correct this will force the use of the Cost Based Optimizer (CBO). This will happen even if the alias or table name is incorrect.
Examples
The examples below use a table CBOTAB with a unique single column index called CBOTAB1 on column COL1.
Correct hint to force use of the index:
explain plan for select /*+ index(cbotab) */ col1 from cbotab; explain plan for select /*+ index(cbotab cbotab1) */ col1 from cbotab; explain plan for select /*+ index(a cbotab1) */ col1 from cbotab a; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=151 INDEX FULL SCAN CBOTAB1 [ANALYZED] Cost=151 Card=10000 Bytes=100000
- The TABLE_NAME is mandatory in the hint.
In the following example the TABLE_NAME was omitted so the index was not used.SQL> explain plan for select /*+ index() */ col1 from cbotab; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=10 TABLE ACCESS FULL CBOTAB [ANALYZED] Cost=10 Card=10000 Bytes=100000
- The INDEX_NAME is optional.
Both of the following examples use the index:explain plan for select /*+ index(cbotab) */ col1 from cbotab; explain plan for select /*+ index(cbotab cbotab1) */ col1 from cbotab; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=151 INDEX FULL SCAN CBOTAB1 [ANALYZED] Cost=151 Card=10000 Bytes=100000
- The table alias MUST be used if the table is aliased in the query
SQL> explain plan for select /*+ index(cbotab) */ col1 from cbotab mytable; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=10 TABLE ACCESS FULL CBOTAB [ANALYZED] Cost=10 Card=10000 Bytes=100000
Correct use of alias in hint:SQL> explain plan for select /*+ index(mytable) */ col1 from cbotab mytable; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=151 INDEX FULL SCAN CBOTAB1 [ANALYZED] Cost=151 Card=10000 Bytes=100000
- If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.
SQL> explain plan for select /*+ index(COBTAB) */ col1 from cbotab; SQL> explain plan for select /*+ index(MITABLE) */ col1 from cbotab mytable; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=10 TABLE ACCESS FULL CBOTAB [ANALYZED] Cost=10 Card=10000 Bytes=100000
- If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be applied.
SQL> explain plan for select /*+ index(cbotab1) */ col1 from cbotab; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=10 TABLE ACCESS FULL CBOTAB [ANALYZED] Cost=10 Card=10000 Bytes=100000
- If a TABLE_NAME is supplied on its own then the optimizer will decide which index to use based on statistics.
explain plan for select /*+ index(cbotab) */ col1 from cbotab; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=151 INDEX FULL SCAN CBOTAB1 [ANALYZED] Cost=151 Card=10000 Bytes=100000
- If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelt correctly then the hint will not be applied even though the TABLE_NAME is correct.
SQL> explain plan for select /*+ index(cbotab COBTAB1) */ col1 from cbotab; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=10 TABLE ACCESS FULL CBOTAB [ANALYZED] Cost=10 Card=10000 Bytes=100000