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)