3

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:

  • DBa(Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
  • DBb(Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)

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

Mario Corchero
  • 5,257
  • 5
  • 33
  • 59
  • 1
    If you see a difference, it probably depends on when your stats were built. I use 11.2 and I've always seen the indexes marked UNUSABLE when truncating (or dropping) a partition. Now, if you are truncating an empty partition that Oracle knows is empty (you built stats after it was empty), then PERHAPS Oracle is smart enough to know that your truncate statement won't affect anything, and simply leaves the indexes as VALID. Just a guess though – tbone Oct 08 '13 at 16:25

3 Answers3

1

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

Mario Corchero
  • 5,257
  • 5
  • 33
  • 59
  • I was checking the data from the statistics which were not up to date. There was one row that was droped and made oracle to mark indexes as invalid. – Mario Corchero Jun 11 '14 at 09:31
0

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

Amit Arora
  • 175
  • 1
  • 1
  • 7
0

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

Mr.P
  • 1,182
  • 3
  • 20
  • 44