I have a function based index that takes 25 minutes to create on a table of 94 Million rows.

When I drop the index, it takes 18 minutes.

Why does it take so long? I would have thought the drop would have been almost immediate? I have noticed that after ~10 minutes, the index is removed from user_indexes, but the script still executes for another ~8 minutes.

The table is partitioned, and this is an Oracle 10g RAC install. If any more information would help, I can add it as required.

Any light shed on this would be greatly appreciated.

  • I'd suspect that your statement is simply waiting for a lock on the table. – a_horse_with_no_name Mar 15 '12 at 18:14
  • According to this: forums.oracle.com/forums/thread.jspa?threadID=541834 I would have expected a resource busy error if it was locked? Also, I have another index which is similar, and if I try to drop both at the same time, I do get that error while dropping the second index. This implies to me that the "drop index" has got the lock it requires, and is just taking its time. – Clarkey Mar 16 '12 at 11:39

A function-based index adds a virtual column to the table (This column is then indexed). Dropping the index removes the virtual column, which leads to a cleanup that takes time (same amount of work as the removal of a non-virtual column).

| improve this answer | |
  • 1
    asktom link: asktom.oracle.com/pls/asktom/… – kubanczyk Mar 16 '12 at 14:51
  • 3
    All those years I believed Oracle was putting the expression into the index. I never knew they were implemented as hidden virtual columns. – a_horse_with_no_name Mar 16 '12 at 15:08
  • Ditto on the lack of knowledge on the virtual columns. Thanks very much for the answer! – Clarkey Mar 20 '12 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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