HINT篇---访问路径相关

二：访问路径相关的hint
1、/*+ FULL(TABLE)*/    表明对表选择全局扫描的方法.
2、/*+ INDEX(TABLE INDEX_NAME) */  表明对表选择索引的扫描方法.
3、/*+ INDEX_ASC(TABLE INDEX_NAME)*/ 表明对表选择索引升序的扫描方法.默认就是升序，所以道理和不加ASC一样。
4、/*+ INDEX_DESC(TABLE INDEX_NAME)*/ 表明对表选择索引降序的扫描方法.
5、/*+ INDEX_FFS(TABLE INDEX_NAME) */ 表明对指定的表执行快速全索引扫描.
6、/*+ INDEX_SS(T T_IND) */   表明当组合索引中，非索引前导列要是用索引时需要指定此hint.
7、/*+ INDEX_SS_ASC(T T_IND) */  表明对表选择索引升序的跳跃式索引扫描方法.
8、/*+ INDEX_SS_DESC(T T_IND) */  表明对表选择索引降序的跳跃式索引扫描方法.
9、/*+ INDEX_JOIN(T1 INDEX1 INDEX2) */ 表明当所有数据都在2个索引内时，使用合并索引
10、/*+ INDEX_COMBINE*/   表明选择位图索引.
11、/*+CLUSTER(TABLE)*/    表明对指定表选择簇扫描，只对簇对象有效.
实验一：关于INDEX_ASC和INDEX_DESC的使用情况
SQL> select * from (select /*+ INDEX_desc(t1 ind_t1_objectid) */ object_id from t1  where object_id is not  null)
  2  where rownum < 10;

 OBJECT_ID
----------
     91329
     91329
     91329
     91329
     91329
     91329
     91329
     91329
     91329

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1864826209

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     9 |    45 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY              |                 |       |       |            |          |
|*  2 |   INDEX FULL SCAN DESCENDING| IND_T1_OBJECTID |     9 |    45 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   2 - filter("OBJECT_ID" IS NOT NULL)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        620  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> select * from (select /*+ INDEX_arc(t1 ind_t1_objectid) */ object_id from t1  where object_id is not  null)
  2  where rownum < 10;

 OBJECT_ID
----------
         2
         2
         2
         2
         2
         2
         2
         2
         2

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 286638431

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     9 |    45 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY        |                 |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_T1_OBJECTID |     9 |    45 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   2 - filter("OBJECT_ID" IS NOT NULL)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        616  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed
结果显而易见，降序和升序是对于当走索引的时候，得到的结果会不一样。由于默认就是ASC的方式，所以如果我们想用order by desc的方式访问表的时候，那我们就可以使用index_desc来代替了。

实验二：/*+ INDEX_SS(T T_IND) */的使用情况
SQL>create table t3 as select object_id,object_name,object_type from dba_objects;
SQL>insert into t3 select  * from t3;
SQL>select object_type,count(*) from t3 group by object_type;
SQL>create index idx_type_id on t3(object_type,object_id);
SQL> select * from t3 where object_id is not null;

607128 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2574254479

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   698K|    59M|  1007   (2)| 00:00:13 |
|*  1 |  TABLE ACCESS FULL| T3   |   698K|    59M|  1007   (2)| 00:00:13 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          4  recursive calls
          2  db block gets
      44030  consistent gets
          0  physical reads
          0  redo size
   31080897  bytes sent via SQL*Net to client
     445749  bytes received via SQL*Net from client
      40477  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     607128  rows processed

SQL> select /*+ INDEX_SS(T3 idx_type_id) */* from t3 where object_id is not null;

607128 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2242485420

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   698K|    59M| 32452   (1)| 00:06:30 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3          |   698K|    59M| 32452   (1)| 00:06:30 |
|*  2 |   INDEX SKIP SCAN           | IDX_TYPE_ID | 34929 |       |  2078   (1)| 00:00:25 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          4  recursive calls
          1  db block gets
     730606  consistent gets
       2081  physical reads
          0  redo size
   33507509  bytes sent via SQL*Net to client
     445749  bytes received via SQL*Net from client
      40477  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     607128  rows processed

实验三：/*+ INDEX_JOIN(T1 INDEX1 INDEX2) */ 
SQL> create table t1 as select object_id,object_name from dba_objects;
SQL> insert into t1 select  * from t1;
SQL> create index ind_t1_objectid on t1(object_id);
SQL> create index idx_t1_name on t1(object_name);
SQL> select /*+ INDEX_join(T1 ind_t1_objectid idx_t1_name) */ object_id,object_name from t1 where object_name is not null and object_id is not null;

4856704 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 474600647

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |  4856K|   134M| 52331   (1)| 00:10:28 |
|   1 |  VIEW                  | index$_join$_001 |  4856K|   134M| 52331   (1)| 00:10:28 |
|*  2 |   HASH JOIN            |                  |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| IND_T1_OBJECTID  |  4856K|   134M| 13953   (1)| 00:02:48 |
|*  4 |    INDEX FAST FULL SCAN| IDX_T1_NAME      |  4856K|   134M| 30047   (1)| 00:06:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(ROWID=ROWID)
   3 - filter("OBJECT_ID" IS NOT NULL)
   4 - filter("OBJECT_NAME" IS NOT NULL)

Statistics
----------------------------------------------------------
        127  recursive calls
          0  db block gets
     236403  consistent gets
      32905  physical reads
          0  redo size
  100728855  bytes sent via SQL*Net to client
    3562104  bytes received via SQL*Net from client
     323782  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    4856704  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24500180/viewspace-1064689/，如需转载，请注明出处，否则将追究法律责任。