Unusable index

After table maintenance, like move or split partition, underlying indexes are marked as unusable.

This boils down to segment reorganisation, not dictionary change.

For instance :

CREATE TABLE t(x NUMBER)
PARTITION BY RANGE(x)
(PARTITION p1 VALUES LESS THAN (MAXVALUE));

CREATE INDEX i ON t (x);



INSERT INTO t VALUES (1);

INDEX_NAME STATUS
---------- --------
I VALID


The partition p1 is splitted into two p2 and p3, all rows from p1 belongs to p2, p3 is created empty, no data manipulation, the index remains valid


delete from t;
INSERT INTO t VALUES (250);

INDEX_NAME STATUS
---------- --------
I VALID

Same here, all rows from p3 moved to p5, p4 is created empty, no problem


delete from t;
INSERT INTO t VALUES (250);
INSERT INTO t VALUES (350);

SELECT index_name, status
FROM user_indexes
WHERE index_name = 'I';
INDEX_NAME STATUS
---------- --------
I UNUSABLE


One row goes to p6, one row to p7, the index is invalidated.

To avoid this, use ALTER TABLE T SPLIT PARTITION p5 AT (300) INTO (PARTITION p6, PARTITION p7) UPDATE INDEXES;

What are the consequences?


INSERT INTO t VALUES (320);
1 row inserted


It does not prevent DML in this case.

select * from t where x=320;

Execution Plan
---------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=ALL_ROWS
1 0 PARTITION RANGE SINGLE
2 1 TABLE ACCESS FULL SCOTT.T


The row is retrieved, but the unusable index is not used.

In some case, DML may be prevented.

alter index i rebuild;
alter table t modify (x primary key using index);
ALTER TABLE T SPLIT PARTITION p7 AT
(330) INTO (PARTITION p8, PARTITION p9) ;

insert into t values (450);
ORA-01502: index 'SCOTT.I' or partition of such index is in unusable state


The index can no longer be used for constraint enforcement and the INSERT fails.

If the index is partitioned, the index is not marked as unusable as a whole, only the affected (sub)partitions are marked.

Check all your indexes status in dba_indexes.status, dba_ind_partitions.status and dba_ind_subpartitions.status.

Rebuild them with alter index i rebuild, alter index i rebuild partition p and alter index i rebuild subpartition sp.