Oracle 11g之不可见的索引

以下内容摘自OTN

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

您常常感到疑惑，索引是否真的有利于用户的查询？它可能有利于一个查询，但会影响 10 个其他查询。索引肯定会对 INSERT 语句造成负面影响，也会执行潜在的删除和更新操作，这取决于WHERE 条件是否在索引中包括该列。

一个相关的问题是，使用索引时，如果该索引被删除，会对查询性能造成什么影响？当然，您可以删除索引并查看对查询的影响，但说起来容易做起来难。索引实际上如何有助于查询？您必须重新定义索引，为此，需要进行重新创建。完全重新创建之后，就没有人能使用它了。重新创建索引也是一个昂贵的过程；它会占用许多有用的数据库资源。

您有办法创建一种对特定查询不可用同时又不会影响其他查询的索引吗？在 Oracle Database 11g 之前，不推荐使用 ALTER INDEX ...UNUSABLE，因为它会使表上的所有 DML 失败。但现在，您可以通过不可见的索引 精确使用该选项。简言之，您可以使索引对优化器“不可见”，这样就没有查询会使用它了。如果查询希望使用索引，则必须将其显式指定为提示。

下面是一个例子。假设有一个名为 RES 的表，并且您创建了如下所示的索引：

SQL> create index in_res_guest on res (guest_id);

分析完该表和索引后，如果您执行

SQL> select * from res where guest_id = 101;

将发现正在使用该索引：

Execution Plan
----------------------------------------------------------
Plan hash value: 1519600902
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    28 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RES          |     1 |    28 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IN_RES_GUEST |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("GUEST_ID"=101)

现在，使索引不可见：

SQL> alter index in_res_guest invisible;
 
Index altered.

现在，将显示以下内容：

SQL> select * from res where guest_id = 101
  2  /
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3824022422
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    28 |   140   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| RES  |     1 |    28 |   140   (2)| 00:00:02 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("GUEST_ID"=101)

未使用索引。要使优化器再次使用索引，您必须在提示中显式命名索引：（ 我测试貌似不起作用，只能通过改变会话参数起作用）

SQL> select /*+ INDEX (res IN_RES_GUEST) */ res_id from res where guest_id = 101;

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    28 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RES          |     1 |    28 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IN_RES_GUEST |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

立刻！优化器再次使用了索引。

或者，您也可以设置会话级参数以使用不可见的索引：

SQL> alter session set optimizer_use_invisible_indexes = true;

如果您无法修改代码（如第三方应用程序中的代码），该特性将十分有用。创建索引时，可以在末尾追加子句 INVISIBLE，将索引构建为对优化器不可见。您还可以使用字典视图 USER_INDEXES 查看索引的当前设置。

SQL> select visibility
  2  from user_indexes
  3 where index_name = 'IN_RES_GUEST';

VISIBILITY
---------
INVISIBLE

注意，如果您重新构建该索引，则该索引将变为可见。您必须再次将其显式设为不可见。

那么，该索引到底对“什么”不可见？当然，它不会对用户不可见。它只是对优化器不可见。常规数据库操作（如插入、更新和删除）将继续更新索引。创建不可见索引时应注意这一点；由于该索引，您将不会再次查看性能，但同时您在 DML 操作期间可能会付出一些代价。

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

以下是我自己做的例子，仅供参考：

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

SCOTT@chan> create table test (id number, name varchar2(20));

表已创建。

SCOTT@chan> insert into test values (1, 'JACK');

已创建 1 行。

SCOTT@chan> insert into test values (2, 'DYLAN');

已创建 1 行。

SCOTT@chan> commit;

提交完成。

SCOTT@chan> create index test_idx on test(name);

索引已创建。

SCOTT@chan> set autotrace on explain
SCOTT@chan> select * from test where name = 'DYLAN';

        ID NAME
---------- --------------------
         2 DYLAN


执行计划
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='DYLAN')

Note
-----
   - dynamic sampling used for this statement (level=2)

SCOTT@chan> alter index test_idx invisible;

索引已更改。

SCOTT@chan> select * from test where name = 'DYLAN';

        ID NAME
---------- --------------------
         2 DYLAN


执行计划
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='DYLAN')

Note
-----
   - dynamic sampling used for this statement (level=2)

 

SCOTT@chan>  alter session set optimizer_use_invisible_indexes = true;

会话已更改。

SCOTT@chan> select * from test where name = 'DYLAN';

        ID NAME
---------- --------------------
         2 DYLAN


执行计划
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='DYLAN')

Note
-----
   - dynamic sampling used for this statement (level=2)

SCOTT@chan> select visibility
  2         from user_indexes
  3         where index_name = 'TEST_IDX';

VISIBILIT
---------
INVISIBLE

 

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

Present  By  Dylan.