Using function based indexes

Using function indexes is not as easy as just coding up a 'create index' statement and waiting for the magic to occur. Use this check list to ensure that they work for you:


The meaning of query_rewrite_enabled is to allow theoptimizer to rewrite the query allowing it to use the function based index. The meaning of query_rewrite_integrity=trusted is to tell the optimizer to 'trust' that the code marked deterministic by the programmer is in fact deterministic.

If the code is in fact not deterministic (that is, it returns different output given the same inputs), the resulting rows from the index may be incorrect.

You must use the Cost Based Optimizer. Function based indexes are only visible to the Cost Based Optimizer and will not be used by the Rule Based Optimizer ever. Use substr() to constrain return values from user written functions that return VARCHAR2 or RAW types. Optionally hide the substr in a view

Once the above list has been satisfied, it is as easy as "CREATE INDEX" from there on in. The optimizer will find and use your indexes at runtime for you.