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 NUMBER
3 );
表已创建。
已用时间: 00: 00: 00.16
SQL> BEGIN
2 FOR i IN 1 .. 10000 LOOP
3 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 calls
4 db block gets
26 consistent gets
0 physical reads
780 redo size
545 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 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 calls
81 db block gets
745 consistent gets
6 physical reads
12564 redo size
545 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
71 sorts (memory)
0 sorts (disk)
1 rows processed
不可见索引可用于具有特定索引需求的过程,其中索引的存在可能对其他功能区域产生不利影响。它们也有助于测试删除索引时的影响。