I'm a novice trying to learn about function-based indexes. Any tips?
For example:
In the query that uses the FBI, make sure you have a IS NOT NULL where clause:
"The optimizer can only use an index (in place of a table) if it knows that the index entries are one-for-one with the table. Because it does not know if the expression may return a NULL (which are NOT stored in the index). It cannot make that direct swap unless you inform the optimizer."
"Hence SELECT ROOT FROM TST_AGG will not have the chance to use the index, but SELECT ROOT FROM TST_AGG WHERE ROOT IS NOT NULL should be able to."
Function Based Index not improving query performance
The function should also handle nulls. For example, if null values get passed into the function (such as a null X or Y), then the function should handle that by returning null — so that the function doesn't error-out. If that happens though, and the function does return nulls, then I think that means the query won't use the index; it'll do a full table scan instead. Unless, as mentioned in #1 above, the query's where clause has a IS NOT NULL .
Any functions used in the index must be defined as deterministic.
If you change the semantics of a DETERMINISTIC function and recompile it, then you must manually rebuild any dependent function-based indexes and materialized views. Otherwise, they report results for the prior version of the function.
Disadvantages of Function-Based Indexes
Edit: A SQL hint might be an option -- to force Oracle to use the FBI:
/*+ INDEX (parcels shape_idx) */
Set a SQL hint to instruct the Oracle optimizer in choosing an execution plan.
Related: Function-based spatial indexes