gotchaMinor
Why does it take a long time to drop a function based index in Oracle?
Viewed 0 times
whytakelongfunctiontimedropdoesbasedindexoracle
Problem
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.
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.
Solution
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).
Context
StackExchange Database Administrators Q#15098, answer score: 8
Revisions (0)
No revisions yet.