关于不可见索引的学习(r3笔记74天)

不可见索引在日常工作中可能实用比较少，自己体验了一把，还是比较实用的功能，在平时的工作中不妨尝试一下。 我们来先体验一下，然后再细细的总结一下。 测试环境基于11gR2 > sqlplus -v SQL*Plus: Release 11.2.0.2.0 Production 我们先来创建一个表来看看，然后创建一个不可见索引，来看看索引的访问情况。 create table t as select *from dba_objects where object_id is not null and rownum<100000; 我们随机抽取5条数据，后续的查询都会基于里面的1条数据来做说明。 select *from (select object_id from t order by dbms_random.value()) where rownum<5; OBJECT_ID ---------- 9445 4672 5048 4096 打开debug，来看看sql语句的执行情况。 set autot trace exp stat 我们选取了object_id为9445的记录。在没有索引的情况下，看看执行情况，好做比对。 select *from t where object_id=9445 ; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 18 recursive calls 0 db block gets 177 consistent gets 0 physical reads 0 redo size 1632 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 上面的语句毫无疑问走了全表扫描，因为我们还没有创建索引。 我们来创建一个唯一性索引，来在查询中启用一下索引，这个时候索引还是可见的。 create unique index inx_t on t(object_id) ; SQL> select *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1855406669 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | INX_T | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 1 physical reads 0 redo size 1499 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 这个时候一切都没有什么特别之处。 我们来尝试下面的步骤。把索引改为不可见索引，或者我们删除已经存在的索引，然后重建一个不可见索引。 --删除,重建索引 drop index inx_t; create unique index inx_t on t(object_id) invisible; --修改索引属性，改为不可见 alter index inx_t invisible; 然后再次尝试运行同样的sql语句。看看是否索引能够正常启用。 select *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 139 consistent gets 0 physical reads 0 redo size 1632 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 这个时候索引没有启用，感觉就跟删除了一样。其实在dml操作中还是会有消耗和正常的索引是一样的，只是在优化器中对这个索引不可见。 我们来使用hint看看是否能够正常启用。 select /*+index(t inx_t)*/ *from t where object_id=9445; SQL> select /*+index(t inx_t)*/ *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 135 consistent gets 0 physical reads 0 redo size 1632 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 结果尽管使用了hint，但是还是不买账，依旧没有反应。 我们来看看怎么使用不可见索引。 默认在数据库参数中，有一个参数optimizer_use_invisible_indexes，默认是不会启用不可见索引的。我们可以从session级别,system级别进行设定。一般都会在session级启用，来查看索引是否能够达到预期目标，然后再决定是否设定为可见。 SQL> show parameter vis NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE 我们在session级启用。 alter session set "optimizer_use_invisible_indexes"=true; 然后执行同样的语句。可以看到索引能够正常启用了。 select *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1855406669 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | INX_T | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1499 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 我们恢复原值。来看看，又开始走了全表扫描。 alter session set "optimizer_use_invisible_indexes"=false; select *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 135 consistent gets 0 physical reads 0 redo size 1632 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 如果在session级别确认了索引能够极大的提高效率，可以修改索引的属性，把索引置为可见状态。 我们设置索引为可见，看看语句的执行情况，索引又能够正常启用了。 alter index inx_t visible; select *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1855406669 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | INX_T | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1499 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 大体的测试就这么多，还有几个问题需要考虑一下，如果不可见索引的使用这么灵活。我们可以根据不可见索引来评估一条sql语句的执行效率情况，那么是否在高可用场景中使用呢。 我们可以做一个简单的测试来说明，在创建不可见索引的时候，是否还存在着锁，这样就能够判断是否适用于高可用场景了。 我们删除表，然后创建新的表，表中的数据尽量多一些，让创建索引的时间稍微长一点。 drop table t; create table t as select *from dba_objects where rownum<5000000; create index inx_t on t(object_id) invisible; 然后我们查看锁的情况。可以看到在创建不可见索引的过程中还是存在着锁。在线业务中还是会有影响的。

SID_SERIAL   ORACLE_USE OBJECT_NAME     LOGON_TIM SEC_WAIT OSUSER     MACHINE    PROGRAM                 STATE              STATUS     LOCK_ MODE_HELD
------------ ---------- --------------- --------- -------- ---------- ---------- -------------------- ---------- ---------- ----- ----------
763,33293    N1         OBJ$            04-DEC-14        0 testuser   testdb    sqlplus@xx (TNS V1-V3)  WAITED SHORT TIME   ACTIVE     DML   Row-X (SX)
763,33293    N1         T               04-DEC-14        0 testuser   testdb    sqlplus@xx (TNS V1-V3)  WAITED SHORT TIME   ACTIVE     DML   Share
763,33293    N1         T               04-DEC-14        0 testuser    testdb   sqlplus@xx (TNS V1-V3)  WAITED SHORT TIME   ACTIVE     DL    Row-X (SX)
763,33293    N1         T               04-DEC-14        0 testuser    testdb   sqlplus@xx (TNS V1-V3)  WAITED SHORT TIME   ACTIVE     DL    Row-X (SX)

不可见索引在数据字典视图中和普通索引没有太大的区别，都有对应的索引段，这是在索引对应的数据字典中有一个字段visibility标明索引是否可见。

SQL> select segment_name,segment_type,blocks from user_segments where segment_name='INX_T'
SEGMENT_NAME         SEGMENT_TYPE           BLOCKS
-------------------- ------------------ ----------
INX_T                INDEX                   10240

SQL> SELECT INDEX_NAME,VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME='INX_T';
INDEX_NAME                     VISIBILIT
------------------------------ ---------
INX_T                          INVISIBLE