ORA-14287 When Trying To Rebuild Partition Of Local Index On Composite Partition Table (Doc ID 2059614.1)

To Bottom

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

SYMPTOMS

Table is composite partitioned table:

e.g.

CREATE TABLE <table name>

			      (deptno number, 

			       deptname varchar2(20),

			       quarterly_sales number(10, 2),

			       state varchar2(2))       

			  PARTITION BY RANGE (deptno)  

			    SUBPARTITION BY LIST (state)

			     subpartition template

			      (SUBPARTITION q1_northwest VALUES ('OR', 'WA'),

			       SUBPARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'))

			  (PARTITION p1  VALUES LESS THAN (1000)  ,

			   PARTITION p2  VALUES LESS THAN (3000)   ,

			    PARTITION p3  VALUES LESS THAN (maxvalue) );

There is a local index on it

e.g.

local index

			CREATE INDEX <index name> on <table name> (deptno,state) LOCAL;

Attempt to rebuild partition of the local index results in error ORA-14287

SH@dw24> alter index <index name> rebuild partition p1;

			alter index ix_sales rebuild partition p1

			            *

			ERROR at line 1:

			ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

CAUSE

The error ORA-14287 is expected in the above scenario.

In a composite partitioned table a table partition and a local index partition are logical containers,
table subpartition and index subpartition are physical containers (segments belongs to each), hence these are the ones that need to be moved or rebuilt.

SOLUTION

Rebuild index subpartition not index partition.

You may query the SUBPARTITION_NAME column of the DBA_INDEX_SUBPARTITIONS view to determine the name of the index subpartitions.

e.g.

ALTER INDEX <index name> REBUILD SUBPARTITION P1_Q1_NORTHWEST;

REFERENCES

NOTE:1481609.1 - Common Questions on Indexes on Partitioned Table