Enable an Function-Based Index

Enable an Function-Based Index example

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

  

-- disable Index using ENABLE
ALTER INDEX CITY_IDX ENABLE;

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	ENABLED

↑