HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle SQL Hints --- Access Path Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

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

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

 

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 |

----------------------------------------------------------------------------------------

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat