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
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
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 |
--------------------------------------------------------------------------------
----------
----------
| 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
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
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 |
----------------------------------------------------------------------------
| 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中的表
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 |
Time |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4 (0)|
00:00:01 |
00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 1 | 8 | 4 (0)|
00:00:01 |
00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_TEST | 1 | | 3 (0)|
00:00:01 |
00:00:01 |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - filter("B"=1)
2 - access("A"=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> 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 |
Time |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4 (0)|
00:00:01 |
00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 1 | 8 | 4 (0)|
00:00:01 |
00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_TEST | 1 | | 3 (0)|
00:00:01 |
00:00:01 |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - filter("B"=1)
2 - access("A"=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;
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步骤
此时把位图再转化为rowid,最后用rowid定位查找到的表记录
大家看位图访问成本很高的,要N步骤
1)| 00:00:12 |
-------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"=1)
6 - access("B"=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;
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)
6 - access("B"=1)
已选择19行。
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-751317/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-751317/