After truncating a single partition its Primary key’s index becomes unusable and all inserts/updates into that partition fail

alter-tableindexoracle-11gpartitioningtruncate

I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001…201212.. and so on). This table has several local Indexes but the problem lies with the PK index for some reason. I have the same DDL in another schema and it works fine there. Not sure what tio look for to resolve this.

I am using the Informatica (ETL tool) to load data into this table. Before loading we do truncate partition table:

SQL> ALTER TABLE owner_name.SAMPLE_PARTITIONED_TBL 
   2 TRUNCATE PARTITION SMPL_201001 DROP STORAGE;

As the load starts I get the following error:

Message: Database errors occurred: 
ORA-01502: index 'owner_name.SAMPLE_PARTITIONED_TBL_PK' or 
partition of such index is in unusable state

Looking at the indexes's status…

SQL> select STATUS from all_indexes
  2  where INDEX_NAME like 'SAMPLE_PARTITIONED_TBL_PK';

STATUS
--------
UNUSABLE

Now after executing SQL> ALTER INDEX owner_name.SAMPLE_PARTITIONED_TBL_PK REBUILD; the local partitioned index returns to STATUS=VALID state the loading can continue without a problem.

UPDATE:

As per @Mat's observation below, I am checking if the PK Index is locally partitioned:

SQL> select * from all_indexes where table_name = 'SAMPLE_PARTITIONED_TBL';

I see SAMPLE_PARTITIONED_TBL_PK and the other indexes in the list.

SQL> select * from  all_part_indexes where table_name = 'SAMPLE_PARTITIONED_TBL';

Here I see all the indexes with LOCALITY='LOCAL'; except SAMPLE_PARTITIONED_TBL_PK is missing in the result set thus confirming Mat's observation. 🙂

Thanks.

Best Answer

That's expected, most DDL operations on partitions will invalidate the indexes affected by the DDL. The ALTER TABLE docs state that on all relevant operations.

Specifically for truncate partition:

For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions or subpartitions are marked UNUSABLE, then the database truncates them and resets the UNUSABLE marker to VALID.

So local indexes are truncated and marked valid. Global indexes are invalidated unless you specifically request that they be maintained. (See further down.)
In your case, it looks like your primary key is not based on a local index – it appears in fact not to be partitioned, since you can't do an alter index ... rebuild on a partitioned index, you need to rebuild each partition. So the invalidation is expected.

To do that, you can add an UPDATE INDEXES / UPDATE GLOBAL INDEXES clause to your ALTER TABLE statement to have Oracle automatically maintain the indexes for you during the ALTER - See Updating Indexes Automatically. There are some limitations though, read the Considerations when Updating Indexes Automatically section carefully.

But that's not always desirable. Sometimes it's more efficient to rebuild the indexes after the load. In that case, your load process can often use the SKIP_UNUSABLE_INDEXES parameter (can be set at session level). (This parameter defaults to TRUE in 11gR2.)

Related Question