Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

1 Answer

up vote 3 down vote accepted

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.)

share|improve this answer
Thanks Mat. Will work on resolving it.. and get back to you. +1 for the references :) – Kent Pawar Dec 10 '12 at 7:29
1  
Updated: looks like your PK isn't locally partitioned (from the docs and from tests here). – Mat Dec 10 '12 at 9:34
Yes, I see the issue now.. Thanks again Mat! Just updated the question above confirming your observations. – Kent Pawar Dec 10 '12 at 11:08
1  
I just reproduced your scenario on a database here since after thinking about it more, it seemed strange that the local indexes would be invalidated for a truncate (truncating them is the only thing necessary). The I saw your rebuild command which confirmed the discrepancy. – Mat Dec 10 '12 at 11:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.