CODEWITHSUNDEEP
oracle-databaseoracle11gdatabase-partitioningdrop-table
Mario Corchero
Mario Corchero

Reputation: 5575

Drop Empty Partition. Are indexes marked as invalid?

I want to drop a partition that is empty but I am aware about oracle setting all indexes to unusable whenever you perform a partition DDL statement like DROP, therefore, I should add UPDATE GLOBAL INDEXES to the statement though it looks unnecessary.

Then I came up with this post where it says that it wont mark it as unusable so I decided to test it. The thing is that I tested it in two oracle versions and it worked different!

Having two instances:

In DBa it marked them as invalid and in DBb which contained the same data than the other db (cloned with exp/imp) it succeed to drop without marking them unusable.

Is it possible to explicitly tell Oracle that you want to keep the indexes usable because there is no data in the partition (without rebuilding the indexes) ?

Upvotes: 3

Views: 2828

Answers (3)

Mr.P
Mr.P

Reputation: 1257

yes .. use LOCAL indexes while creating indexes over partitioned table

Upvotes: 0

Mario Corchero
Mario Corchero

Reputation: 5575

So far I am not able to find out why it was marked as invalid in a placed but not in the other one but there is something to say in case someone have the same problem.

Run it always with UPDATE GLOBAL INDEXES since if the partition is empty it will take no time to perform the drop and it ensures that the indexes will not be marked as invalid. Therefore, there is no reason to hope that oracle won't mark them

Upvotes: 1

Amit Arora
Amit Arora

Reputation: 175

May be you can try below, this maintains index validity during the drop. ALTER TABLE t1 DROP PARTITION p5 UPDATE GLOBAL INDEXES;

Upvotes: 0

Related Questions