Create Function-Based Index

Create Function-Based Index example

  
-- create Index Function-Based
CREATE INDEX CITY_IDX
  ON STUDENTS (UPPER(CITY));
  
CREATE INDEX PRICE_IDX 
   ON COURSE(PRICE - DISCOUNT);

Output:

index CITY_IDX created.
index PRICE_IDX created.

Check indexes

select INDEX_NAME, INDEX_TYPE, FUNCIDX_STATUS
from USER_INDEXES
WHERE INDEX_NAME IN 
  (SELECT OBJECT_NAME FROM 
    (SELECT * 
    FROM USER_OBJECTS 
    WHERE OBJECT_TYPE='INDEX' 
    ORDER BY CREATED DESC) 
  WHERE ROWNUM < =2);

Output:

INDEX_NAME	INDEX_TYPE	FUNCIDX_STATUS
PRICE_IDX	FUNCTION-BASED NORMAL	ENABLED
CITY_IDX	FUNCTION-BASED NORMAL	ENABLED

↑