[转帖]删除分区如何不让全局索引失效？

删除,分区,如何,不让,全局,索引,失效 · 浏览次数 : 0

小编点评

**删除分区，会导致全局索引失效，但如何避免？** 当我们删除表中数据的时候，同时要删除他对应的索引，由于索引是有序排列的，如果要删除一条索引数据，他的组织结构，就需要调整，以保证正确的排列顺序。 1. **重建索引，让其生效** - SQL> alter index idx_01 rebuild online; 2. **再次执行分区删除的操作** - SQL> alter table interval_sale drop partition; 3. **再看全局索引，他的状态正常，VALID，并未因为分区删除的操作，导致其失效** - SQL> select table_name, index_name, status 2 from user_indexes where table_name='INTERVAL_SALE';TABLE_NAME INDEX_NAME STATUS--------------- --------------- --------INTERVAL_SALE IDX_01 VALID 4. **通过以上实验，可以得到结论，如果待删除的分区中没有任何数据，执行分区删除，不会导致全局索引状态的失效**

正文

记得上次ACOUG年会(《ACOUG年会感想》)，请教杨长老问题的时候，谈到分区，如果执行分区删除的操作，就会导致全局索引失效，除了使用12c以上版本能避免这个问题外，指出另外一种解决的方式，表面看很巧妙，实则是对分区原理的深入理解。

我们先从实验，了解这个问题，首先创建分区表，他存在4个分区，每个分区中，都存在数据，


SQL> CREATE TABLE interval_sale
  2  ( prod_id        NUMBER(6)
  3  , cust_id        NUMBER
  4  , time_id        DATE
  5  )
  6  PARTITION BY RANGE (time_id)
  7  INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
  8    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')),
  9      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')),
 10      PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),
 11      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')));
 
SQL> insert into interval_sale values(1, 1, to_date('2002-01-01','yyyy-mm-dd'));
1 row created.
 
SQL> insert into interval_sale values(2, 2, to_date('2003-01-01','yyyy-mm-dd'));
1 row created.
 
SQL> insert into interval_sale values(3, 3, to_date('2004-01-01','yyyy-mm-dd'));
1 row created.
 
SQL> insert into interval_sale values(4, 4, to_date('2005-01-01','yyyy-mm-dd'));
1 row created.
 
SQL> commit;
Commit complete.

创建全局索引，当前状态是VALID，


SQL> create index idx_01 on interval_sale(cust_id);
Index created.
 
SQL> select table_name, index_name, partitioned, status
  2  from user_indexes where table_name='INTERVAL_SALE';
TABLE_NAME       INDEX_NAME      PARTITIONED  STATUS
--------------- --------------- ------------ --------
INTERVAL_SALE    IDX_01          NO           VALID
删除第一个分区，


SQL> alter table interval_sale drop partition for (to_date('2002-01-01','yyyy-mm-dd'));
Table altered.

此时，看到这个全局索引是UNUSABLE的状态，和我们的设想是相同的，即删除分区，会导致全局索引的失效，


SQL> select table_name, index_name, status
  2  from user_indexes where table_name='INTERVAL_SALE';
TABLE_NAME       INDEX_NAME      STATUS
--------------- --------------- ----------
INTERVAL_SALE    IDX_01          UNUSABLE

结论告诉我们，删除分区，确实会导致全局索引的失效，我们从问题入手，为什么分区删除，会导致全局索引的失效？

我们知道，Oracle中索引是以B树的结构存储的，包括了索引键值、rowid信息，而且按照索引键值有序排列，当通过索引扫描需要回表的时候，能利用rowid直接定位到索引键值对应的数据块，这是最快的数据访问方式。当我们删除表中数据的时候，同时要删除他对应的索引，由于索引是有序排列的，如果要删除一条索引数据，他的组织结构，就需要调整，以保证正确的排列顺序，12c之前，因为某种原因，无法在删除分区的同时，对索引重新构建，所以此时索引的状态是失效的，与其是错的，宁可不让用，删除分区，需要手工rebuild重建索引才能让其生效，

我们换种思路，之所以全局索引的状态失效，根本问题就是索引对应的分区中数据被删除了，那么，如果不删除分区中的数据，索引结构无需任何调整，他的状态是不是就是正常的？

首先重建索引，让其生效，


SQL> alter index idx_01 rebuild online;
Index altered.
 
SQL> select table_name, index_name, status
  2  from user_indexes where table_name='INTERVAL_SALE';
TABLE_NAME       INDEX_NAME      STATUS
--------------- --------------- --------
INTERVAL_SALE    IDX_01          VALID

此时，通过delete删除即将删除的第二个分区的数据，


SQL> delete from interval_sale where time_id <= to_date('2003-01-01','yyyy-mm-dd');
1 row deleted.
 
SQL> commit;
Commit complete.

再次执行分区删除的操作，


SQL> alter table interval_sale drop partition for (to_date('2003-01-01','yyyy-mm-dd'));
Table altered.

此时，再看全局索引，他的状态正常，VALID，并未因为分区删除的操作，导致其失效，


SQL> select table_name, index_name, status
  2  from user_indexes where table_name='INTERVAL_SALE';
TABLE_NAME       INDEX_NAME      STATUS
--------------- --------------- --------
INTERVAL_SALE    IDX_01          VALID

通过以上实验，可以得到结论，如果待删除的分区中没有任何数据，执行分区删除，不会导致全局索引状态的失效。原因已经说了，因为分区删除时，不存在任何数据需要删除，意味着无需调整索引结构，所以全局索引的状态，就无需置为失效，这个算是对待分区删除避免全局索引失效的一种另类解决方案了。

通过这个问题，能让我体会到的，就是一个看着很简单的问题背后，其实蕴涵着丰富的知识，同时对待任何一个知识点，从原理层理解地越深入，找到问题的本质，就可以让你和真相更近，豁然开朗，这可能就需要日常的积累，碰到问题的时候，多问一句为什么，就可能让你大开眼界，这就是Oracle以及技术领域最吸引人的地方了。

</article>