Oracle11g 中关于索引的可见性（Invisible Indexes）

Oracle11g 中关于索引的可见性（Invisible Indexes）

在Oracle 11g中允许标记索引为不可见。不可见索引的维护方法和其它索引相同，但是在实例级别（instance）或者会话级别（session）当初始化参数 OPTIMIZER_USE_INVISIBLE_INDEXES 被设置为TRUE时索引的不可见性会被忽略。当创建不可见索引时可以使用INVISIBLE关键字；可以使用ALTER INDEX命令来修改索引的 可见/不可见性。

默认情况下，OPTIMIZER_USE_INVISIBLE_INDEXES的值为FALSE

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES;NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_use_invisible_indexes      boolean                FALSE

SQL语句的语法如下： 

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

 示例如下：

创建一个测试表test_tab，并插入数值；然后在ID列上创建一个不可见索引（invisible index）

SQL> conn scott/tiger@coffeebean;
已连接。
SQL> set linesize 200;
SQL> set pagesize 999;
SQL> set timing on;
SQL> set autotrace on;
SQL>
SQL> CREATE TABLE test_tab (2    id  NUMBER3  );表已创建。已用时间:  00: 00: 00.16
SQL> BEGIN2    FOR i IN 1 .. 10000 LOOP3      INSERT INTO test_tab VALUES (i);4    END LOOP;5    COMMIT;6  END;7  /PL/SQL 过程已成功完成。已用时间:  00: 00: 00.69
SQL> CREATE INDEX test_tab_id ON test_tab(id) INVISIBLE;索引已创建。已用时间:  00: 00: 00.30
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'test_tab', cascade=> TRUE);PL/SQL 过程已成功完成。已用时间:  00: 00: 01.07

然后执行一个简单的查询，在WHERE条件中使用了索引列 ID：

SQL> SELECT * FROM test_tab WHERE id = 9999;ID
----------9999已用时间:  00: 00: 00.13执行计划
----------------------------------------------------------
Plan hash value: 3962208483------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |     1 |     4 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("ID"=9999)统计信息
----------------------------------------------------------6  recursive calls4  db block gets26  consistent gets0  physical reads780  redo size545  bytes sent via SQL*Net to client608  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1  rows processed

通过查询计划，我们看到，执行了全表扫描（TABLE ACCESS FULL），没有使用索引。

然后修改参数设置：

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;会话已更改。已用时间:  00: 00: 00.00

再次执行相同的查询，这次我们发现使用了索引：

SQL> SELECT * FROM test_tab WHERE id = 9999;ID
----------9999已用时间:  00: 00: 00.28执行计划
----------------------------------------------------------
Plan hash value: 134160084--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_TAB_ID |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("ID"=9999)Note
------ SQL plan baseline "SQL_PLAN_8d45j0zxf5q2acf9c3cbb" used for this statement统计信息
----------------------------------------------------------561  recursive calls81  db block gets745  consistent gets6  physical reads12564  redo size545  bytes sent via SQL*Net to client608  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client71  sorts (memory)0  sorts (disk)1  rows processed

不可见索引可用于具有特定索引需求的过程，其中索引的存在可能对其他功能区域产生不利影响。它们也有助于测试删除索引时的影响。