Why does it take a long time to drop a function based index in Oracle?
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).