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

--------------------------------------------------------------------------------

-------

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>

ITPub博客

oracle hint_no_index

最新文章