Disable an Function-Based Index

Disable an Function-Based Index example

To disable an oracle database Function-Based index you must use the command alter index with DISABLE keyword. The status of index will change from enable to disable.

  

-- disable Index using DISABLE
ALTER INDEX CITY_IDX DISABLE;

Output:

index CITY_IDX altered.

Check indexes

select INDEX_NAME, INDEX_TYPE, STATUS, FUNCIDX_STATUS
from USER_INDEXES
WHERE table_name='STUDENTS';

Output:

INDEX_NAME	INDEX_TYPE	STATUS	FUNCIDX_STATUS
PK_STUDENT_ID	NORMAL	VALID	(null)
CITY_IDX	FUNCTION-BASED NORMAL	VALID	DISABLED

↑