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

最新推荐文章于 2025-01-10 17:37:46 发布

FU_ZHONGYUAN

最新推荐文章于 2025-01-10 17:37:46 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： less 前端 css

原文链接：https://blog.youkuaiyun.com/bisal/article/details/103856220

oracle 专栏收录该内容

1 篇文章

订阅专栏

本文通过实验探讨了在Oracle中，删除分区如何导致全局索引失效，以及如何通过理解分区原理来避免这一问题。在12c之前，删除分区会使得全局索引变为UNUSABLE状态，需要手动重建。但若在删除分区前先删除该分区的数据，再执行分区删除操作，则全局索引状态可保持VALID。这种方法展示了对分区和索引工作原理的深入理解，提供了一种避免索引失效的策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

记得上次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的状态，和我们的设想是相同的，即删除分区，会导致全局索引的失效，

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

https://blog.youkuaiyun.com/bisal/article/details/103856220