删除分区如何不让全局索引失效？

待我称王封你为后i 2023-06-27 11:17 37阅读 0赞

记得上次ACOUG年会(《[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重建索引才能让其生效，

![format_png][]

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

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

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以及技术领域最吸引人的地方了。

[ACOUG]: https://blog.csdn.net/bisal/article/details/103675746
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW51THAwbVZwdmM4Z0xXUUlUd2liSG1hYXUxVmw4am93NUJZOHg0ZDBzSFpmaWFuY05VNzVZRllla3drREVpY0dxdFVDTkZ5TTZxVGlhblBRLzY0MA?x-oss-process=image/format,png