what is a functional index

Function-based indexes can use any Function or Object method that is declared as repeatable. Function‐based indexes give us the ability to index computed columns and use these indexes in a query.

Queries using expressions can use the index. Example              CREATE INDEX sales_margin_inx                        ON sales (revenue - cost); SELECT ordid    FROM sales WHERE (revenue - cost) > 1000;

We have to enable Function-based indexes by enabling the following initialization parameters:                  ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE; ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; Function based Index