HINT篇---访问路径相关

最新推荐文章于 2022-02-10 11:23:40 发布

cri3224

最新推荐文章于 2022-02-10 11:23:40 发布

阅读量163

点赞数

二：访问路径相关的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