索引列的usable和visible(11g引入的新特性)

转自

http://blog.itpub.net/17203031/viewspace-688135/

http://blog.itpub.net/17203031/viewspace-688136/

http://blog.itpub.net/17203031/viewspace-688137/

索引是Oracle数据库中一个重要的组成部分。Oracle优化和处理很多方面都是围绕索引进行的。在Oracle11g中，索引添加了一个visible属性，来加强对索引的控制能力。今天我们来一起聊聊这个新加入的visible属性和原有的usable属性。

usable属性是Oracle一直以来控制索引效应的重要因素。索引是一种依赖数据表字段取值的对象，有效的索引需要实时维护数据与索引之间的关系。在进行数据表DML操作的时候，Oracle会自动进行数据表对应索引的更新。

如果我们将数据表特定索引属性设置为unusable，也就是取消索引的使用。那么效果会有三个：

  •        在数据表进行DML操作的时候，连带的时不会对unusable属性索引进行更新的。也就是说，索引一旦被unusable，就存在和数据表索引列不对应的可能
  •         当Oracle进行执行路径生成的时候，是不会选择unusable的属性引导路径；
  •         如果在SQL语句中，强制Oracle使用特定unusable的索引（比如使用hint），那么Oracle会报错说索引已经被unusable；

 

引起索引状态unusable的原因很多。

  1. 比如直接修改alter index，将索引属性变化为unusable。
  2. 还有就是进行数据表move操作，引发数据行rowid变化，此时index自动变化为unusable。
  3. 还有就是如使用分区表的时候，如果创建的是全局索引，如果其中一个分区被删除，全局索引也会被unusable。

将索引恢复为正确状态，只能通过rebuild重建索引。重新收集数据表索引列的信息，重新构建索引树。

invisible从某种程度上看，和unusable有相似之处，都是一定程度上的对索引禁用。作为一个11g引入的新特性，我们一起来实验研究。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0    Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

SQL> select count(*) from stktestonhand;

  COUNT(*)

----------

200100

SQL> desc stktestonhand;

Name              Type               Nullable Default Comments

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

（无相关字段省略……）

FORMCODE          VARCHAR2(255 CHAR) Y                        

SERIESNUMBER_FROM VARCHAR2(10 CHAR)  Y                        

SERIESNUMBER_TO   VARCHAR2(10 CHAR)  Y                        

TICKET_QUANTITY   NUMBER(19,2)       Y                          

CREATE_USER       VARCHAR2(20)       Y                        

SEQ_NUMBER        NUMBER(13)                                  

 

数据表stktestonhand就是我们的实验表。数据列seriesnumber_from和seriesnumber_to是组合唯一的（业务限定），所以，构建索引对象如下：

-- Create/Recreate primary, unique and foreign key constraints

alter table STKTESTONHAND

  add constraint pk_stktestonhand_seq_number primary key (SEQ_NUMBER);

-- Create/Recreate indexes

create unique index ind_stktestonhand_comp0 on STKTESTONHAND (seriesnumber_from, seriesnumber_to);

 

我们进行搜索查询实验。

SQL> explain plan for select * from stktestonhand where SERIESNUMBER_FROM<=lpad('10000',10,'0') and SERIESNUMBER_TO>=lpad('10000',10,'0');

Executed in 0.016 seconds

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3449092418

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

| Id  | Operation                   | Name                    | Rows  | Bytes |

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

|   0 | SELECT STATEMENT            |                         |   183 | 11346 |

|   1 |  TABLE ACCESS BY INDEX ROWID| STKTESTONHAND           |   183 | 11346 |

|*  2 |   INDEX RANGE SCAN          | IND_STKTESTONHAND_COMP0 |   183 |       |

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

Predicate Information (identified by operation id):

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

   2 - access("SERIESNUMBER_TO">='0000010000' AND "SERIESNUMBER_FROM"<='00000100 filter("SERIESNUMBER_TO">='0000010000')

Executed in 0.156 seconds

此时，该查询根据索引路径进行搜索。下面，我们观察索引的状态。

SQL> col index_name format a20;

SQL> col table_name format a15;

SQL> select index_name, table_name,UNIQUENESS,status, num_rows, VISIBILITY from user_indexes where index_name='IND_STKTESTONHAND_COMP0';

INDEX_NAME           TABLE_NAME      UNIQUENESS STATUS     NUM_ROWS VISIBILITY

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

IND_STKTESTONHAND_CO STKTESTONHAND   UNIQUE     VALID        200100 VISIBLE

MP0                                                                 

Executed in 0.062 seconds

SQL> col segment_name format a15;

SQL> select segment_name, segment_type,bytes,blocks from user_segments where segment_name='IND_STKTESTONHAND_COMP0';

SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS

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

IND_STKTESTONHA INDEX                 8388608       1024

ND_COMP0                                     

Executed in 0.125 seconds

注意：当前索引状态为VALID，可见性为visible。索引体积为8.3MB。我们首先观察unusable的现象。

SQL> alter index IND_STKTESTONHAND_COMP0 unusable;

Executed in 0.032 seconds

 

SQL> select index_name, table_name,UNIQUENESS,status, num_rows, VISIBILITY from user_indexes where index_name='IND_STKTESTONHAND_COMP0';

INDEX_NAME           TABLE_NAME      UNIQUENESS STATUS     NUM_ROWS VISIBILITY

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

IND_STKTESTONHAND_CO STKTESTONHAND   UNIQUE     UNUSABLE     200100 VISIBLE

MP0                                                                 

Executed in 0.046 seconds

 

SQL> explain plan for select * from stktestonhand where SERIESNUMBER_FROM<=lpad('10000',10,'0') and SERIESNUMBER_TO>=lpad('10000',10,'0');

Executed in 0 seconds

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2311662756

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

| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time

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

|   0 | SELECT STATEMENT  |               |   183 | 11346 |   588   (2)| 00:00:0

|*  1 |  TABLE ACCESS FULL| STKTESTONHAND |   183 | 11346 |   588   (2)| 00:00:0

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

Predicate Information (identified by operation id):

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

   1 - filter("SERIESNUMBER_FROM"<='0000010000' AND "SERIESNUMBER_TO">='0000010000')

Executed in 0.094 seconds

从上面的信息中，可以知道。一旦索引被unusable，SQL执行计划中不会考虑索引路径，相当于失效。

注意下面的实验，笔者发现，如果此时对数据表进行DML操作，数据库会报错拒绝。

SQL> insert into stktestonhand (SEQ_NUMBER, FORMCODE, SERIESNUMBER_FROM, SERIESNUMBER_TO, TICKET_QUANTITY, REASONCODE_SEQ, CREATE_USER, CREATE_DATE, ALLOCATION_DATE)

  2  values (0,'000','100000000','1200000',32,43,'TEST',sysdate,sysdate);

 

insert into stktestonhand (SEQ_NUMBER, FORMCODE, SERIESNUMBER_FROM, SERIESNUMBER_TO, TICKET_QUANTITY, REASONCODE_SEQ, CREATE_USER, CREATE_DATE, ALLOCATION_DATE)

values (0,'000','100000000','1200000',32,43,'TEST',sysdate,sysdate)

ORA-01502: 索引 'NBSTEST.IND_STKTESTONHAND_COMP0' 或这类索引的分区处于不可用状态

 

这部分和我们原来对于unusable的理解存在差异，这个问题我们暂时留待下面解决。如果要恢复索引，需要重建对象。

SQL> alter index IND_STKTESTONHAND_COMP0 rebuild ;

Executed in 0.609 seconds

 

SQL> select index_name, table_name,UNIQUENESS,status, num_rows, VISIBILITY from user_indexes where index_name='IND_STKTESTONHAND_COMP0';

INDEX_NAME           TABLE_NAME      UNIQUENESS STATUS     NUM_ROWS VISIBILITY

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

IND_STKTESTONHAND_CO STKTESTONHAND   UNIQUE     VALID        200100 VISIBLE

MP0                                                                

下面我们实验invisible特性。

SQL> alter index ind_stktestonhand_comp0 invisible;

Executed in 0 seconds

 

SQL> select index_name, table_name,UNIQUENESS,status, num_rows, VISIBILITY from user_indexes where index_name='IND_STKTESTONHAND_COMP0';

INDEX_NAME           TABLE_NAME      UNIQUENESS STATUS     NUM_ROWS VISIBILITY

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

IND_STKTESTONHAND_CO STKTESTONHAND   UNIQUE     VALID        200100 INVISIBLE

MP0                                                                 

Executed in 0.062 seconds

此时，我们使用原有的SQL语句进行检索。

SQL> explain plan for select * from stktestonhand where SERIESNUMBER_FROM<=lpad('10000',10,'0') and SERIESNUMBER_TO>=lpad('10000',10,'0');

Executed in 0 seconds

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2311662756

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

| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time

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

|   0 | SELECT STATEMENT  |               |   183 | 11346 |   588   (2)| 00:00:0

|*  1 |  TABLE ACCESS FULL| STKTESTONHAND |   183 | 11346 |   588   (2)| 00:00:0

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

Predicate Information (identified by operation id):

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

   1 - filter("SERIESNUMBER_FROM"<='0000010000' AND "SERIESNUMBER_TO">='0000010000')

Executed in 0.141 seconds

同unusable一样，当索引被设置为invisible之后，Oracle优化器在生成执行计划的时候，是不会选择索引路径的。那么，invisible的索引是否和数据表还保持的DML同步呢？

SQL> insert into stktestonhand (SEQ_NUMBER, FORMCODE, SERIESNUMBER_FROM, SERIESNUMBER_TO, TICKET_QUANTITY, REASONCODE_SEQ, CREATE_USER, CREATE_DATE, ALLOCATION_DATE)

  2  values (0,'000','100000000','1200000',32,43,'TEST',sysdate,sysdate)

  3  ;

Executed in 0 seconds //对invisible索引的对象，是允许插入数据的。

（调用专门脚本，添加数据…）

SQL> select count(*) from stktestonhand;

  COUNT(*)

----------

    300100   //从原来的20万行，增加到30万

Executed in 0.047 seconds

 

SQL> select segment_name, segment_type,bytes,blocks from user_segments where segment_name='IND_STKTESTONHAND_COMP0';

SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS

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

IND_STKTESTONHA INDEX                11534336       1408

ND_COMP0                                     

Executed in 0.031 seconds

我们数据量从原来的20万增加到30万，同时索引对象的体积也变化到了11MB。说明，即使索引被invisible，不参与执行计划生成，但是会随着源数据表的DML操作而同步。

到此，我们得到了结论：invisible属性的作用就是让索引对象不参与进Oracle优化器执行计划生成过程，但是会随着数据表DML操作而更新。这点是与unusable的最大区别！

 

恢复属性，执行下面语句:

SQL> alter index ind_stktestonhand_comp0 visible;

Executed in 0.015 seconds

反思：站在Oracle的角度，想下为什么Oracle要提供这个invisible功能。如果没有这个属性，当我们需要暂时性的取消索引（调试、系统整理和实验），只能够删除索引或者unusable索引，让索引完全失效。这样虽然可以实现失效目的，但是带来一定的性能问题。

如果需要索引重新应用，相当于重建rebuild索引对象。我们反复说过，索引是一种有负担的对象，对一些海量数据表，重建数据表是很消耗时间和资源的，而且还伴随着一系列的锁操作。这对一些OLTP系统，特别是业务高峰期的系统，这种风险是不能轻易承受的。

invisible的出现，主要还是Oracle11g中对online特性支持的一种体现。索引虽然被invisible不可见了，但是只是一个标志的问题，索引本身还会和数据表维持联系。这样，进行索引失效切换动作，也会更加平滑安全。

下面我们回到遇到的索引失效后，不能更新DML操作的问题。

经过分析，笔者感觉问题的原因可能处在索引IND_STKTESTONHAND_COMP0的unique特性上。下面进行如下实验。

-- Create/Recreate indexes

drop index IND_STKTESTONHAND_COMP0;

create index IND_STKTESTONHAND_COMP0 on STKTESTONHAND (SERIESNUMBER_FROM, SERIESNUMBER_TO);

 

重新建立了索引对象，并且设置为normal类型。记住下面的统计属性。

SQL> select index_name, table_name,UNIQUENESS,status, num_rows, VISIBILITY from user_indexes where index_name='IND_STKTESTONHAND_COMP0';

INDEX_NAME           TABLE_NAME      UNIQUENESS STATUS     NUM_ROWS VISIBILITY

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

IND_STKTESTONHAND_CO STKTESTONHAND   NONUNIQUE  VALID        300100 VISIBLE

MP0                                                                

 

Executed in 0.063 seconds

SQL> select segment_name, segment_type,bytes,blocks from user_segments where segment_name='IND_STKTESTONHAND_COMP0';

 

SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS

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

IND_STKTESTONHA INDEX                12582912       1536

ND_COMP0                                     

Executed in 0.063 seconds

 

判断索引生效语句。

 

SQL> explain plan for select * from stktestonhand where SERIESNUMBER_FROM<=lpad('10000',10,'0') and SERIESNUMBER_TO>=lpad('10000',10,'0');

Executed in 0.016 seconds

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3449092418

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

| Id  | Operation                   | Name                    | Rows  | Bytes |

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

|   0 | SELECT STATEMENT            |                         |   183 | 11346 |

|   1 |  TABLE ACCESS BY INDEX ROWID| STKTESTONHAND           |   183 | 11346 |

|*  2 |   INDEX RANGE SCAN          | IND_STKTESTONHAND_COMP0 |   275 |       |

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

Predicate Information (identified by operation id):

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

   2 - access("SERIESNUMBER_TO">='0000010000' AND "SERIESNUMBER_FROM"<='00000100

       filter("SERIESNUMBER_TO">='0000010000')

Executed in 0.172 seconds

索引生效。当我们设置unusable属性。

 

SQL> alter index IND_STKTESTONHAND_COMP0 unusable;

Executed in 0.031 seconds

 

SQL> explain plan for select * from stktestonhand where SERIESNUMBER_FROM<=lpad('10000',10,'0') and SERIESNUMBER_TO>=lpad('10000',10,'0');

Executed in 0.016 seconds

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2311662756

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

| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time

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

|   0 | SELECT STATEMENT  |               |   183 | 11346 |   588   (2)| 00:00:0

|*  1 |  TABLE ACCESS FULL| STKTESTONHAND |   183 | 11346 |   588   (2)| 00:00:0

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

Predicate Information (identified by operation id):

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

   1 - filter("SERIESNUMBER_FROM"<='0000010000' AND

              "SERIESNUMBER_TO">='0000010000')

Executed in 0.14 seconds

 

SQL> select index_name, table_name,UNIQUENESS,status, num_rows, VISIBILITY from user_indexes where index_name='IND_STKTESTONHAND_COMP0';

INDEX_NAME           TABLE_NAME      UNIQUENESS STATUS     NUM_ROWS VISIBILITY

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

IND_STKTESTONHAND_CO STKTESTONHAND   NONUNIQUE  UNUSABLE     300100 VISIBLE

MP0                                                                

 

Executed in 0.046 seconds

属性失效。此时我们的DML操作是否成功呢？

 

SQL> delete stktestonhand where seq_number<1000;

Executed in 0.031 seconds

 

SQL> insert into stktestonhand (seq_number) values (1000000);

Executed in 0 seconds

 

SQL> commit;

Executed in 0.015 seconds

 

SQL> delete stktestonhand where rownum<20000;

Executed in 0.765 seconds

 

SQL> commit;

Executed in 0.016 seconds

 

注意，当我们将unique类型索引变为normal类型之后。一旦索引被unusable，数据表的DML操作可以进行。那么，索引连带的DML更新会进行吗？

SQL> select index_name, table_name,UNIQUENESS,status, num_rows, VISIBILITY from user_indexes where index_name='IND_STKTESTONHAND_COMP0';

 

INDEX_NAME           TABLE_NAME      UNIQUENESS STATUS     NUM_ROWS VISIBILITY

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

IND_STKTESTONHAND_CO STKTESTONHAND   NONUNIQUE  UNUSABLE     300100 VISIBLE

MP0                   //连带的数据字典信息没有更新；

Executed in 0.031 seconds

 

SQL> select count(*) from stktestonhand;

 

  COUNT(*)

----------

    280102 //确定有数据被删除！

 

Executed in 0.062 seconds

 

SQL> select segment_name, segment_type,bytes,blocks from user_segments where segment_name='IND_STKTESTONHAND_COMP0';

//Oracle已经不将这个索引对象作为空间段成员了！

SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS

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

Executed in 0.015 seconds

可见，一旦索引对象被unusable，连带的DML操作不会更新。要想使用只有进行重建。

SQL> alter index IND_STKTESTONHAND_COMP0 rebuild;

Executed in 0.749 seconds

 

SQL> select segment_name, segment_type,bytes,blocks from user_segments where segment_name='IND_STKTESTONHAND_COMP0';

SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS

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

IND_STKTESTONHA INDEX                11534336       1408

ND_COMP0                                     

 

Executed in 0.062 seconds

 

上面的实验证明了，unusable属性使用之后，索引对象和数据表的关系被完全切断，连带DML操作不会进行。如果需要重新起效，需要rebuild索引对象（这对于海量数据表往往是繁重的工作）。同时,unusable属性在处理不同类型索引的时候有些差异。如果是unique类型索引，被unusable后，数据表的DML操作被禁止。一般normal索引，是可以继续进行DML操作的。

posted @ 2015-03-27 22:41  princessd8251  阅读(1351)  评论(0)    收藏  举报