Oracle SQL Hints --- Access Path Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 04:01:57

Share to 

Access Path Hints CLUSTER Usage: CLUSTER([<@Block>] <Table>) Description: Instructs the optimizer to use a cluster scan to access the specified cluster table HELLODBA.COM>exec sql_explain('SELECT /*+cluster(T)*/* FROM T_EEE T where A >:1', 'BASIC OUTLINE');   -------------------------------------------- | Id  | Operation            | Name        | -------------------------------------------- |   0 | SELECT STATEMENT     |             | |   1 |  TABLE ACCESS CLUSTER| T_EEE       | |   2 |   INDEX RANGE SCAN   | C_KEY2_IDX1 | -------------------------------------------- HASH       Usage: HASH([<@Block>] <Table>) Description: Instructs the optimizer to use a hash scan to access the specified hash cluster table HELLODBA.COM>exec sql_explain('SELECT /*+ hash(a) full(d) */* FROM T_AAA A, T_DDD D WHERE a.c=d.c', 'BASIC OUTLINE');   ------------------------------------ | Id  | Operation          | Name  | ------------------------------------ |   0 | SELECT STATEMENT   |       | |   1 |  NESTED LOOPS      |       | |   2 |   TABLE ACCESS FULL| T_DDD | |   3 |   TABLE ACCESS HASH| T_AAA | ------------------------------------ ROWID Usage: ROWID([<@Block>] <Table>) Description: Instructs the optimizer to use rowid location to access the specified table directly HELLODBA.COM>exec sql_explain('select /*+rowid(o)*/* from t_objects o where rowid <= :1 and object_id=100', 'BASIC OUTLINE');   ------------------------------------------------- | Id  | Operation                   | Name      | ------------------------------------------------- |   0 | SELECT STATEMENT            |           | |   1 |  TABLE ACCESS BY ROWID RANGE| T_OBJECTS | ------------------------------------------------- FULL Usage: FULL([<@Block>] <Table>) Description: Instructs the optimizer to use a table scan to access the specified table HELLODBA.COM>exec sql_explain('select /*+full(o)*/* from t_objects o where rowid = :1 and object_id>100','BASIC OUTLINE');   --------------------------------------- | Id  | Operation         | Name      | --------------------------------------- |   0 | SELECT STATEMENT  |           | |   1 |  TABLE ACCESS FULL| T_OBJECTS | --------------------------------------- INDEX Usage: INDEX([<@Block>] <Table> [ <Index>]) or INDEX([<@Block>] <Table> [(<Indexed Columns>)]) Description: Instructs the optimizer to use an index scan to access the specified table HELLODBA.COM>exec sql_explain('select /*+index(o (object_id))*/* from t_objects o where rowid = :1 and object_id>100','BASIC OUTLINE');   ---------------------------------------------------- | Id  | Operation                   | Name         | ---------------------------------------------------- |   0 | SELECT STATEMENT            |              | |   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS    | |   2 |   INDEX RANGE SCAN          | T_OBJECTS_PK | ---------------------------------------------------- INDEX_ASC Usage: INDEX_ASC([<@Block>] <Table> [ <Index>]) or INDEX_ASC([<@Block>] <Table> [(<Indexed Columns>)]) Description: Instructs the optimizer to use an index scan to access the specified table. If it uses an index range scan, then it scans the index in ascending order of the indexed values. Demo: (Please not the output sequence) HELLODBA.COM>select /*+index_asc(o (object_id))*/object_id, object_name from t_objects o where object_id<5;    OBJECT_ID OBJECT_NAME ---------- --------------------------------------------------------------------------------           2 C_OBJ#           3 I_OBJ#           4 TAB$ INDEX_DESC Usage: INDEX_DESC([<@Block>] <Table> [ <Index>]) or INDEX_DESC([<@Block>] <Table> [( <Indexed Columns>)]) Description: Instructs the optimizer to use an index scan to access the specified table. If it uses an index range scan, then it scans the index in descending order of the indexed values. Demo: (Please not the output sequence) HELLODBA.COM>select /*+index_desc(o (object_id))*/object_id, object_name from t_objects o where object_id<5;    OBJECT_ID OBJECT_NAME ---------- --------------------------------------------------------------------------------          4 TAB$          3 I_OBJ#          2 C_OBJ# NO_INDEX Usage: NO_INDEX([<@Block>] <Table> [ <Index>]) or NO_INDEX([<@Block>] <Table> [( <Indexed Columns>)]) Description: Prevents the optimizer to use an index scan to access the specified table. HELLODBA.COM>exec sql_explain('select /*+no_index(o t_objects_pk)*/object_id, object_name from t_objects o where object_id < 10', 'BASIC OUTLINE');   ----------------------------------------------- | Id  | Operation            | Name           | ----------------------------------------------- |   0 | SELECT STATEMENT     |                | |   1 |  INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | ----------------------------------------------- INDEX_FFS Usage: INDEX_FFS([<@Block>] <Table> [ <Index>]) or INDEX_FFS([<@Block>] <Table> [( <Indexed Columns>)]) Description: Instructs the optimizer to use a fast full index scan to access the specified table. HELLODBA.COM>exec sql_explain('SELECT /*+INDEX_FFS(t t_objects_IDX8)*/object_name FROM t_objects t where owner=:A', 'BASIC OUTLINE');   ----------------------------------------------- | Id  | Operation            | Name           | ----------------------------------------------- |   0 | SELECT STATEMENT     |                | |   1 |  INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | ----------------------------------------------- NO_INDEX_FFS Usage: NO_INDEX_FFS([<@Block>] <Table> [ <Index>]) or NO_INDEX_FFS([<@Block>] <Table> [( <Indexed Columns>)]) Description: Prevents the optimizer to use a fast full index scan to access the specified table. HELLODBA.COM>exec sql_explain('select /*+no_index_ffs(o t_objects_idx8)*/owner, object_name from t_objects o', 'BASIC OUTLINE');   --------------------------------------- | Id  | Operation         | Name      | --------------------------------------- |   0 | SELECT STATEMENT  |           | |   1 |  TABLE ACCESS FULL| T_OBJECTS | --------------------------------------- INDEX_RRS Usage: INDEX_RRS([<@Block>] <Table> [ <Index>]) or INDEX_RRS([<@Block>] <Table> [( <Indexed Columns>)]) Description: This hint normally works in the internal statement when performing parallel fast full index scan query. Demo: (Only works in 9i) HELLODBA.COM>create table t (A number, B varchar2(20), constraint t_pk primary key(A) ) organization index parallel;   Table created. HELLODBA.COM>select count(*) from t;   Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1    0   SORT (AGGREGATE)    2    1     SORT* (AGGREGATE)                                                   :Q17628000    3    2       INDEX* (FAST FULL SCAN) OF 'T_PK' (UNIQUE) (Cost=1 Card=1000000) :Q17628000    2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) FROM (SELECT /*+ INDEX_RRS(A2 "T_PK") */ 0 FROM "T"  PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)A2) A1    3 PARALLEL_COMBINED_WITH_PARENT INDEX_SS Usage: INDEX_SS([<@Block>] <Table> [ <Index>]) or INDEX_SS([<@Block>] <Table> [( <Indexed Columns>)]) Description: Instructs the optimizer to use an index skip scan to access the specified table. HELLODBA.COM>exec sql_explain('select /*+index_ss(t t_tables_pk)*/count(status) from t_tables t where table_name like :A', 'BASIC OUTLINE');   ---------------------------------------------------- | Id  | Operation                    | Name        | ---------------------------------------------------- |   0 | SELECT STATEMENT             |             | |   1 |  SORT AGGREGATE              |             | |   2 |   TABLE ACCESS BY INDEX ROWID| T_TABLES    | |   3 |    INDEX SKIP SCAN           | T_TABLES_PK | ---------------------------------------------------- INDEX_SS_ASC         Usage: INDEX_SS_ASC([<@Block>] <Table> [ <Index>]) or INDEX_SS_ASC([<@Block>] <Table> [( <Indexed Columns>)]) Description: Instructs the optimizer to use an index skip scan to access the specified table. And it scans the index in ascending order of the indexed values. Demo: (Please not the output sequence) HELLODBA.COM>select /*+index_ss_asc(t t_tables_pk)*/table_name, status from t_tables t where table_name like 'T%' and rownum<=3;   TABLE_NAME                     STATUS ------------------------------ -------- TAB$                           VALID TABCOMPART$                    VALID TABLE_PRIVILEGE_MAP            VALID INDEX_SS_DESC      Usage: IINDEX_SS_DESC([<@Block>] <Table> [ <Index>]) or INDEX_SS_DESC([<@Block>] <Table> [( <Indexed Columns>)]) Description: Instructs the optimizer to use an index skip scan to access the specified table. And it scans the index in descending order of the indexed values. Demo: (Please not the output sequence) HELLODBA.COM>select /*+index_ss_desc(t t_tables_pk)*/table_name, status from t_tables t where table_name like 'T%' and rownum<=3;   TABLE_NAME                     STATUS ------------------------------ -------- TYPE_MISC$                     VALID TYPEHIERARCHY$                 VALID TYPED_VIEW$                    VALID NO_INDEX_SS Usage: NO_INDEX_SS([<@Block>] <Table> [ <Index>]) or NO_INDEX_SS([<@Block>] <Table> [( <Indexed Columns>)]) Description: Prevents the optimizer to use an index skip scan to access the specified table. HELLODBA.COM>exec sql_explain('select /*+no_index_ss(o t_objects_idx8)*/owner, object_name from t_objects o where object_name like :A', 'BASIC OUTLINE');   ----------------------------------------------- | Id  | Operation            | Name           | ----------------------------------------------- |   0 | SELECT STATEMENT     |                | |   1 |  INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | ----------------------------------------------- INDEX_RS_ASC        Usage: INDEX_RS_ASC([<@Block>] <Table> [ <Index>]) or INDEX_RS_ASC([<@Block>] <Table> [( <Indexed Columns>)]) Description: Instructs the optimizer to use an index range scan to access the specified table. And it scans the index in ascending order of the indexed values. If the selected index is a unique index, and predication fulfills unique scan requirements, it will use index unique scan. Demo: (Please not the output sequence) HELLODBA.COM>select /*+index_rs_asc(o t_objects_pk)*/object_id, object_name from t_objects o where object_id < 5;    OBJECT_ID OBJECT_NAME ---------- --------------------------------------------------------------------------------------------          2 C_OBJ#          3 I_OBJ#          4 TAB$ INDEX_RS_DESC Usage: INDEX_RS_DESC([<@Block>] <Table> [ <Index>]) or INDEX_RS_DESC([<@Block>] <Table> [( <Indexed Columns>)]) Description: Instructs the optimizer to use an index range scan to access the specified table. And it scans the index in descending order of the indexed values. If the selected index is a unique index, and predication fulfills unique scan requirements, it will use index unique scan. Demo: (Please not the output sequence) HELLODBA.COM>select /*+index_rs_desc(o t_objects_pk)*/object_id, object_name from t_objects o where object_id < 5;    OBJECT_ID OBJECT_NAME ---------- --------------------------------------------------------------------------------------------          4 TAB$          3 I_OBJ#          2 C_OBJ# AND_EQUAL   Usage: AND_EQUAL([<@Block>] <Table> [<Index 1> <Index 2> ...]) or AND_EQUAL([<@Block>] <Table> [(<Index 1 columns>) (<Index 2 columns>) ...]) Description: Instructs the optimizer to get intersection of ROWID sets from 2 or more single-column indexes. The duplicated ROWID will be eliminated. HELLODBA.COM>exec sql_explain('select /*+AND_EQUAL(t T_TABLES_IDX1 T_TABLES_IDX3)*/* from t_tables t where t.owner=:A and t.tablespace_name=:B', 'BASIC OUTLINE');   ----------------------------------------------------- | Id  | Operation                   | Name          | ----------------------------------------------------- |   0 | SELECT STATEMENT            |               | |   1 |  TABLE ACCESS BY INDEX ROWID| T_TABLES      | |   2 |   AND-EQUAL                 |               | |   3 |    INDEX RANGE SCAN         | T_TABLES_IDX1 | |   4 |    INDEX RANGE SCAN         | T_TABLES_IDX3 | ----------------------------------------------------- INDEX_COMBINE Usage: INDEX_COMBINE([<@Block>] <Table> [<Index 1> ...]) or AND_EQUAL([<@Block>] <Table> [(<Index 1 columns>) ...]) Description: Instructs the optimizer to combine the bitmap to access the specified table.  If the specified index is not bitmap index, it will try to convert the ROWIDs to bitmap first. HELLODBA.COM>exec sql_explain('SELECT /*+INDEX_COMBINE(t t_objects_idx2 t_objects_idx8)*/1 FROM t_objects t where status=:A and owner=:B', 'BASIC OUTLINE');   ---------------------------------------------------------- | Id  | Operation                       | Name           | ---------------------------------------------------------- |   0 | SELECT STATEMENT                |                | |   1 |  BITMAP CONVERSION TO ROWIDS    |                | |   2 |   BITMAP AND                    |                | |   3 |    BITMAP INDEX SINGLE VALUE    | T_OBJECTS_IDX2 | |   4 |    BITMAP CONVERSION FROM ROWIDS|                | |   5 |     SORT ORDER BY               |                | |   6 |      INDEX RANGE SCAN           | T_OBJECTS_IDX8 | ---------------------------------------------------------- INDEX_JOIN Usage: INDEX_JOIN([<@Block>] <Table> [<Index1> <Index2> ...]) or INDEX_JOIN([<@Block>] <Table> [(<Index1 columns>) (<Index2 columns>) ...]) Description: Instructs the optimizer to join indexes. HELLODBA.COM>exec sql_explain('SELECT /*+INDEX_JOIN(t t_objects_idx2 t_objects_idx8)*/1 FROM t_objects t where status=:A and owner=:B', 'BASIC OUTLINE');   ---------------------------------------------------------- | Id  | Operation                     | Name             | ---------------------------------------------------------- |   0 | SELECT STATEMENT              |                  | |   1 |  VIEW                         | index$_join$_001 | |   2 |   HASH JOIN                   |                  | |   3 |    BITMAP CONVERSION TO ROWIDS|                  | |   4 |     BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX2   | |   5 |    INDEX RANGE SCAN           | T_OBJECTS_IDX8   | ---------------------------------------------------------- BITMAP_TREE Usage: BITMAP_TREE([<@Block>] <Table> AND(<Index1>[ <Index2> ...])) or BITMAP_TREE([<@Block>] <Table> AND((<Index1 columns>)[ (<Index2 columns>) ...])) Description: Instructs the optimizer to convert ROWIDs to bitmap, then performance bitmap operations. HELLODBA.COM>exec sql_explain('select /*+BITMAP_TREE(T_OBJECTS AND(T_OBJECTS_IDX4 T_OBJECTS_IDX2))*/ owner from t_objects where owner like :A and status like :B', 'TYPICAL OUTLINE');   ----------------------------------------------------------------------------------------------- | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                |  1190 | 15470 |   559   (1)| 00:00:03 | |   1 |  TABLE ACCESS BY INDEX ROWID | T_OBJECTS      |  1190 | 15470 |   559   (1)| 00:00:03 | |   2 |   BITMAP CONVERSION TO ROWIDS|                |       |       |            |          | |   3 |    BITMAP AND                |                |       |       |            |          | |   4 |     BITMAP MERGE             |                |       |       |            |          | |*  5 |      BITMAP INDEX RANGE SCAN | T_OBJECTS_IDX4 |       |       |            |          | |   6 |     BITMAP MERGE             |                |       |       |            |          | |*  7 |      BITMAP INDEX RANGE SCAN | T_OBJECTS_IDX2 |       |       |            |          | ----------------------------------------------------------------------------------------------- USE_INVISIBLE_INDEXES Usage: USE_INVISIBLE_INDEXES([<@Block>] <Table> ([<Index1>] ...)) or USE_INVISIBLE_INDEXES([<@Block>] <Table> ([<Index1 Columns>] ...)) Description: Instructs the optimizer to use the invisible indexes. HELLODBA.COM>show parameter visible   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes      boolean     FALSE HELLODBA.COM>alter index t_objects_pk invisible;   Index altered.   HELLODBA.COM>exec sql_explain('select /*+qb_name(M) USE_INVISIBLE_INDEXES(o (object_id))*/count(1) from t_objects o where object_id < 1000', 'TYPICAL OUTLINE');   ---------------------------------------------------------------------------------- | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |              |     1 |     5 |     3   (0)| 00:00:01 | |   1 |  SORT AGGREGATE   |              |     1 |     5 |            |          | |*  2 |   INDEX RANGE SCAN| T_OBJECTS_PK |   973 |  4865 |     3   (0)| 00:00:01 | ----------------------------------------------------------------------------------   NO_USE_INVISIBLE_INDEXES Usage: NO_USE_INVISIBLE_INDEXES([<@Block>] <Table> ([<Index1>] ...)) or NO_USE_INVISIBLE_INDEXES([<@Block>] <Table>([<Index1 Columns>] ...)) Description: Prevents the optimizer to use the invisible indexes. HELLODBA.COM>exec sql_explain('select /*+qb_name(M) NO_USE_INVISIBLE_INDEXES(o (t_objects_pk))*/count(1) from t_objects o where object_id < 1000', 'TYPICAL OUTLINE');   ---------------------------------------------------------------------------------------- | Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |                |     1 |     5 |   185   (2)| 00:00:02 | |   1 |  SORT AGGREGATE       |                |     1 |     5 |            |          | |*  2 |   INDEX FAST FULL SCAN| T_OBJECTS_IDX8 |   973 |  4865 |   185   (2)| 00:00:02 | ----------------------------------------------------------------------------------------