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 3 down vote favorite
1

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.

share|improve this question
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

1 Answer

up vote 5 down vote accepted

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

share|improve this answer
asktom link: asktom.oracle.com/pls/asktom/… – kubanczyk Mar 16 '12 at 14:51
1  
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

 
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.