oracle hint系列9
no_index
1,如果指定了表中一个索引,表中其它索引仍可选用
2,如果指定了表中所有的索引,不会使用表中所有的索引
3,如果未指定索引,不会使用表中所有的索引,即会走此表的全表扫描
4,如果同时指定了no_index和index,会选用index hint
no_index
1,如果指定了表中一个索引,表中其它索引仍可选用
2,如果指定了表中所有的索引,不会使用表中所有的索引
3,如果未指定索引,不会使用表中所有的索引,即会走此表的全表扫描
4,如果同时指定了no_index和index,会选用index hint
SQL> explain plan for select /*+ no_index(t_index) */ * from t_index where a=1 a
nd b=2 and c=3;
nd b=2 and c=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 97131188
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_INDEX | 1 | 39 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_INDEX | 1 | 39 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
1 - filter("A"=1 AND "B"=2 AND "C"=3)
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择17行。
SQL>
SQL> explain plan for select /*+ no_index(t_index idx_a idx_c) */ * from t_index
where a=1 and b=2 and c=3;
SQL> explain plan for select /*+ no_index(t_index idx_a idx_c) */ * from t_index
where a=1 and b=2 and c=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 3564556495
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:
00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_INDEX | 1 | 39 | 1 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | IDX_B | 1 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - filter("A"=1 AND "C"=3)
2 - access("B"=2)
2 - access("B"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择19行。
SQL> explain plan for select /*+ no_index(t_index idx_a idx_b idx_c) */ * from t
_index where a=1 and b=2 and c=3;
_index where a=1 and b=2 and c=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 97131188
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_INDEX | 1 | 39 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_INDEX | 1 | 39 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
1 - filter("A"=1 AND "B"=2 AND "C"=3)
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择17行。
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-751473/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-751473/