分区表的什么操作会导致索引失效 Why index became invalid? (Doc ID 1576700.1) APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.2 and later GOAL Indexes should have status as VALID to become usable by queries however sometimes it may have
status as UNUSABLE and this note explains those causes. SOLUTION Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild.
These DBA tasks that shift table ROWID's include: Table partition maintenance - Alter commands (move, split or truncate partition) will shift ROWID's,
making the index invalid and unusable. CTAS maintenance - Table reorganization with "alter table move" or an online table reorganization
(using the dbms_redefinition package) will shift ROWIDs, creating unusable indexes. Oracle imports - An Oracle import (imp utility) with the skip_unusable_indexes=y parameter SQL*Loader (sqlldr utility) - Using direct path loads (e.g. skip_index_maintenance) will cause invalid and unusable indexes --参考bisal bisal的个人杂货铺,删除分区如何不让全局索引失效？ CREATE TABLE interval_sale ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE ) PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR')) ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')), PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')), PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')), 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')); SQL> insert into interval_sale values(2, 2, to_date('2003-01-01','yyyy-mm-dd')); SQL> insert into interval_sale values(3, 3, to_date('2004-01-01','yyyy-mm-dd')); SQL> insert into interval_sale values(4, 4, to_date('2005-01-01','yyyy-mm-dd')); SQL> commit; 创建全局索引，当前状态是VALID SQL> create index idx_01 on interval_sale(cust_id); SQL> select table_name, index_name, partitioned, status from user_indexes where table_name='INTERVAL_SALE'; TABLE_NAME INDEX_NAME PAR STATUS ------------------------------ ------------------------------ --- -------- INTERVAL_SALE IDX_01 NO VALID 删除第一个分区 SQL> alter table interval_sale drop partition for (to_date('2002-01-01','yyyy-mm-dd')); 此时，看到这个全局索引是UNUSABLE的状态，和我们的设想是相同的，即删除分区，会导致全局索引的失效 SQL> select table_name, index_name, partitioned, status from user_indexes where table_name='INTERVAL_SALE'; TABLE_NAME INDEX_NAME PAR STATUS ------------------------------ ------------------------------ --- -------- INTERVAL_SALE IDX_01 NO UNUSABLE ---删除数据 SQL> alter index idx_01 rebuild online; SQL> delete from interval_sale where time_id <= to_date('2003-01-01','yyyy-mm-dd'); SQL> commit; --再删除分区操作 SQL> alter table interval_sale drop partition for (to_date('2003-01-01','yyyy-mm-dd')); 此时，再看全局索引，他的状态正常，VALID，并未因为分区删除的操作，导致其失效 SQL> select table_name, index_name, status from user_indexes where table_name='INTERVAL_SALE'; TABLE_NAME INDEX_NAME STATUS ------------------------------ ------------------------------ -------- INTERVAL_SALE IDX_01 VALID 应用场景反思？ 此测试如何更好的用于实践，设想一个场景; 一个很大的分区表，并且存在主键列索引，非分区及全局索引，需要删除历史分区。 并且历史分区数据不大的情况下，先delete数据后，再删除分区是最好的，因为不用整体维护索引。 如何判断具体的分区数据 select count(*) as 记录数 from 表名 partition(分区名) ;