oracle hint_no_index

oracle hint系列9
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;

已解釋。

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 |
-----------------------------------------------------------------------------

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)

已選擇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> 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)

Note
-----
   - 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;

已解釋。

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 |
-----------------------------------------------------------------------------

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)

已選擇17行。

SQL>