Enabling Oracle Function Based Indexes

A few days ago, I described a simple example of how to create and use Oracle Function Based Indexes. However, and this is a big ‘however’, before you can use Oracle Function Based Indexes, there are several tasks to complete in setting up your database correctly.

• You must have the system privilege query rewrite to create function based indexes on tables in your own schema.
• You must have the system privilege global query rewrite to create function based indexes on tables in other schemas.
• For the optimizer to use function based indexes, the following session or system variables must be set:
  
  QUERY_REWRITE_ENABLED=TRUE
  QUERY_REWRITE_INTEGRITY=TRUSTED

You may enable these at either the session level with ALTER SESSION or at the system level via ALTER SYSTEM or by setting them in the init.ora parameter file. The meaning of query_rewrite_enabled is to allow the optimizer to rewrite the query allowing it to use the function based index. The meaning of 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.

• 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 (recommended).

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.