oracle hint之full,index,index_asc,index_desc,index_combile示例

oracle hint之full,index,index_asc,index_desc,index_combile示例

1,full hint
SQL> explain plan for select a,b from t_test where a=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 3273242040
--------------------------------------------------------------------------------
----------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT            |            |     1 |     8 |     4   (0)|
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST     |     1 |     8 |     4   (0)|
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_TEST |     1 |       |     3   (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
---------------------------------------------------
   2 - access("A"=3)
已选择14行。
SQL> explain plan for select /*+ full(t_test) */  a,b from t_test where a=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 2796558804
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     8 |   306   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T_TEST |     1 |     8 |   306   (2)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
   1 - filter("A"=3)
已选择13行。
SQL>
hash int
hash提示对指定的表进行hash 扫描,它仅适用于cluster table中的表

index hint
index提示如果对表指定多个索引列表,oracle仅会选取成本最低的索引进行扫描表,而不会同时使用列表中所有索引,可以用index_combile
SQL> explain  plan for select a,b from t_test where a=1 and b=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3273242040
--------------------------------------------------------------------------------
----------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |     8 |     4   (0)|
00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_TEST     |     1 |     8 |     4   (0)|
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_TEST |     1 |       |     3   (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"=1)
   2 - access("A"=1)
已选择15行。
--oracle依旧仅应用一个索引,而非a,b列的两个索引
SQL> explain  plan for select  /*+ index(t_test idx_t_test idx_t_b) */  a,b from
 t_test where a=1 and b=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3273242040
--------------------------------------------------------------------------------
----------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |     8 |     4   (0)|
00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_TEST     |     1 |     8 |     4   (0)|
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_TEST |     1 |       |     3   (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"=1)
   2 - access("A"=1)
已选择15行。

index_combile hint
1,连接多个索引进行访问表
2,以位图方式访问表
3,如果不指定索引,oracle使用经评估后最小成本的索引组合来访问表
SQL> explain  plan for select  /*+ index_combine(t_test idx_t_test idx_t_b) */
a,b from t_test where a=1 and b=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3260031232
-------------------------------------------------------------------------------
--------------
| Id  | Operation                       | Name       | Rows  | Bytes | Cost (%C
U)| Time     |
-------------------------------------------------------------------------------
--------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |            |     1 |     8 |   951
1)| 00:00:12 |
|   1 |  BITMAP CONVERSION TO ROWIDS    |            |     1 |     8 |   951
1)| 00:00:12 |
|   2 |   BITMAP AND                    |            |       |       |
  |          |
|   3 |    BITMAP CONVERSION FROM ROWIDS|            |       |       |
  |          |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

|*  4 |     INDEX RANGE SCAN            | IDX_T_TEST |       |       |     3
0)| 00:00:01 |
|   5 |    BITMAP CONVERSION FROM ROWIDS|            |       |       |
  |          |
|*  6 |     INDEX RANGE SCAN            | IDX_T_B    |       |       |   946  --先查找单个索引,然后转化为位图,继而把位图进行与操作,即把两个索引进行组合运算,因为where条件是and
                                                                                此时把位图再转化为rowid,最后用rowid定位查找到的表记录
                                                                                大家看位图访问成本很高的,要N步骤
1)| 00:00:12 |
-------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"=1)
   6 - access("B"=1)
已选择19行。
 
 
 
SQL> explain  plan for select  /*+ index_combine(t_test) */  a,b from t_test wh
re a=1 and b=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3260031232
-------------------------------------------------------------------------------
--------------
| Id  | Operation                       | Name       | Rows  | Bytes | Cost (%C
U)| Time     |
-------------------------------------------------------------------------------
--------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |            |     1 |     8 |   951
1)| 00:00:12 |
|   1 |  BITMAP CONVERSION TO ROWIDS    |            |     1 |     8 |   951
1)| 00:00:12 |
|   2 |   BITMAP AND                    |            |       |       |
  |          |
|   3 |    BITMAP CONVERSION FROM ROWIDS|            |       |       |
  |          |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

|*  4 |     INDEX RANGE SCAN            | IDX_T_TEST |       |       |     3
0)| 00:00:01 |
|   5 |    BITMAP CONVERSION FROM ROWIDS|            |       |       |
  |          |
|*  6 |     INDEX RANGE SCAN            | IDX_T_B    |       |       |   946
1)| 00:00:12 |
-------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"=1)
   6 - access("B"=1)
已选择19行。
SQL>

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

转载于:http://blog.itpub.net/9240380/viewspace-751317/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值