Oracle SQL提示含义与示例 --- 访问路径提示

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2013-01-08 04:01:57

分享到 

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。 访问路径提示 CLUSTER 语法：CLUSTER([<@查询块>] <表>) 描述：指示优化器通过簇来访问表，仅对簇表有效 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       语法：HASH([<@查询块>] <表名>) 描述：指示优化器通过哈希簇来访问表，仅对哈希簇表有效 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 语法：ROWID([<@查询块>] <表>) 描述：指示优化器通过ROWID来访问和扫描表；                            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 语法：FULL([<@查询块>] <表>) 描述：指示优化器采用全表扫描的方式访问表 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 语法：INDEX([<@查询块>] <表> [<索引>]) 或者 INDEX([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器通过索引来访问和扫描表。 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 语法：INDEX_ASC([<@查询块>] <表> [<索引>]) 或者 INDEX_ASC([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器通过索引来访问和扫描表，如果是索引范围扫描，则以索引键值的递增顺序扫描索引记录； 示例：（注意输出结果顺序） 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 语法：INDEX_DESC([<@查询块>] <表> [<索引>]) 或者 INDEX_DESC([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器通过索引来访问和扫描表，如果是索引范围扫描，则以索引键值的递减顺序扫描索引记录； 示例：（注意输出结果顺序） 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 语法：NO_INDEX([<@查询块>] <表> [<索引>]) 或者 NO_INDEX([<@查询块>] <表> [(<索引字段列表>)]) 描述：禁止优化器使用索引扫描访问表。 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 语法：INDEX_FFS([<@查询块>] <表> [<索引>]) 或者 INDEX_FFS([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器以索引快速完全扫描的方式访问表 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 语法：NO_INDEX_FFS([<@查询块>] <表> [<索引>]) 或者 NO_INDEX_FFS([<@查询块>] <表> [(<索引字段列表>)]) 描述：禁止优化器使用索引快速完全扫描访问表。 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 语法：INDEX_RRS([<@查询块>] <表> [<索引>]) 或者 INDEX_RRS([<@查询块>] <表> [(<索引字段列表>)]) 描述：这一提示通常是由优化器在并行查询，加在并行串行直接输入输出时，生成的内部查询语句上，控制对索引快速完全扫描的并行操作。 示例：（以下示例在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 语法：INDEX_SS([<@查询块>] <表> [<索引>]) 或者 INDEX_SS([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器通过跳跃索引扫描来访问表； 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         语法：INDEX_SS_ASC([<@查询块>] <表> [<索引>]) 或者 INDEX_SS_ASC([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器通过跳跃索引扫描来访问表，并以索引键值的递增顺序扫描索引记录； 示例：（注意返回数据顺序） 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      语法：IINDEX_SS_DESC([<@查询块>] <表> [<索引>]) 或者 INDEX_SS_DESC([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器通过跳跃索引扫描来访问表，并以索引键值的递减顺序扫描索引记录； 示例：（注意返回数据顺序） 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 语法：NO_INDEX_SS([<@查询块>] <表> [<索引>]) 或者 NO_INDEX_SS([<@查询块>] <表> [(<索引字段列表>)]) 描述：禁止优化器使用跳跃索引扫描访问表。 示例： 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        语法：INDEX_RS_ASC([<@查询块>] <表> [<索引>]) 或者 INDEX_RS_ASC([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器通过范围索引扫描来访问表，并以索引键值的递增顺序扫描索引记录；但如果谓词条件满足唯一键索引扫描的条件时，优化器还是会选择唯一键索引扫描。 示例：（注意返回数据顺序） 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 语法：INDEX_RS_DESC([<@查询块>] <表> [<索引>]) 或者 INDEX_RS_DESC([<@查询块>] <表> [(<索引字段列表>)]) 描述：指示优化器通过范围索引扫描来访问表，并以索引键值的递减顺序扫描索引记录；但如果谓词条件满足唯一键索引扫描的条件时，优化器还是会选择唯一键索引扫描。 示例：（注意返回数据顺序） 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   语法：AND_EQUAL([<@查询块>] <表> [<索引1> <索引2> ...]) 或者 AND_EQUAL([<@查询块>] <表> [(<索引1字段>) (<索引2字段>) ...]) 描述：指示优化器对由指定表上的两个或多个单字段索引获取ROWID数据集的取交集，并消除重复的ROWID。 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 语法：INDEX_COMBINE([<@查询块>] <表> [<索引1> ...]) 或者 AND_EQUAL([<@查询块>] <表> [(<索引1字段列表>) ...]) 描述：指示优化器以位图计算方式访问表。如果指定了索引，则尝试从指定索引上获取位图数据，如果指定索引不是位图索引，则尝试进行位图转换。 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 语法：INDEX_JOIN([<@查询块>] <表> [<索引1> <索引2> ...]) 或者 INDEX_JOIN([<@查询块>] <表> [(<索引1字段列表>) (<索引2字段列表>) ...]) 描述：指示优化器对由指定表上的两个或多个索引关联后访问表。 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 语法：BITMAP_TREE([<@查询块>] <表> AND(<索引1>[ <索引2> ...])) 或者 BITMAP_TREE([<@查询块>] <表> AND((<索引1字段列表>)[ (<索引2字段列表>) ...])) 描述：指示优化器将ROWID转换为位图，并做位图计算； 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 语法：USE_INVISIBLE_INDEXES([<@查询块>] <表> ([<索引1>] ...)) 或者 USE_INVISIBLE_INDEXES([<@查询块>] <表> ([<索引列表1>] ...)) 描述：指示优化器使用不可见（INVISIBLE）的索引。 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 语法：NO_USE_INVISIBLE_INDEXES([<@查询块>] <表> ([<索引1>] ...)) 或者 NO_USE_INVISIBLE_INDEXES([<@查询块>] <表> ([<索引列表1>] ...)) 描述：禁止优化器使用不可见（INVISIBLE）的索引。 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 | ----------------------------------------------------------------------------------------