索引列的usable和visible（三）

 

下面我们回到遇到的索引失效后，不能更新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操作的。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-688137/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-688137/