Why does it take a long time to drop a function based index in Oracle

version <= 11.2? No.*

But in 12c, there is a new feature called Asynchronous Global Index Maintenance.

The DROP PARTITION operation completes immediately, leaving the global index in usable state. The trick is, that index entries are not maintained, they are orphaned, and these orphaned entries will be cleaned up later. The cleanup process happens automatically in the maintenance window, or it can be started manually.

More details about this: https://richardfoote.wordpress.com/category/asynchronous-global-index-maintenance/

*: There is, but I don't think it is applicable in your case. Deleting (and NOT truncating) all rows from the partition before dropping it leaves the global index in usable state. But this is enormous extra work for the 25% of a 3 TB table.

Setup:

create table t3 (c1 number, c2 number);
alter table t3 add  supplemental log group sl1 (c1, c2) always;
alter table t3 add constraint t3_uk unique (c1, c2);
insert into t3 values (1, 2);
commit;

Try to drop the column:

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

So far so good, goes as expected. Drop the constraint:

SQL> alter table t3 drop constraint t3_uk;

Table altered.

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

Still doesn't work, same error. Check the supplemental log group:

select * from user_log_groups where table_name = 'T3'

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE      ALWAYS      GENERATED
----- -------------- ---------- ------------------- ----------- --------------
BP    SL1            T3         USER LOG GROUP      ALWAYS      USER NAME

Drop the supplemental log group, then try dropping the column again:

SQL> alter table t3 drop supplemental log group sl1;

Table altered.

SQL> alter table t3 drop column c2;

Table altered.