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.