Oracle Function Based Indexes

A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.

Notes:

• You must set the QUERY_REWRITE_ENABLED initialization parameter to TRUE.
• The index is more effective if you gather statistics for the table or schema, using the procedures in the DBMS_STATS package.
• The index cannot contain any null values. Either make sure the appropriate columns contain no null values, or use the NVL function in the index expression to substitute some other value for nulls.

The expression indexed by a function-based index can be an arithmetic expression or an expression that contains a PL/SQL function, package function, C callout, or SQL function. Function-based indexes also support linguistic sorts based on collation keys, efficient linguistic collation of SQL statements, and case-insensitive sorts.

Like other indexes, function-based indexes improve query performance. For example, if you need to access a computationally complex expression often, then you can store it in an index. Then when you need to access the expression, it is already computed. 

Function-based indexes have all of the same properties as indexes on columns. However, unlike indexes on columns which can be used by both cost-based and rule-based optimization, function-based indexes can be used by only by cost-based optimization. 

Advantages of Function-Based Indexes

Function-based indexes:

• Increase the number of situations where the optimizer can perform a range scan instead of a full table scan. For example, consider the expression in this WHERE clause:

  
  CREATE INDEX Idx ON Example_tab(Column_a + Column_b);
  
  SELECT * FROM Example_tab WHERE Column_a + Column_b < 10; 
  

  The optimizer can use a range scan for this query because the index is built on (column_a + column_b). Range scans typically produce fast response times if the predicate selects less than 15% of the rows of a large table. The optimizer can estimate how many rows are selected by expressions more accurately if the expressions are materialized in a function-based index. (Expressions of function-based indexes are represented as virtual columns andANALYZE can build histograms on such columns.)
• Precompute the value of a computationally intensive function and store it in the index. An index can store computationally intensive expression that you access often. When you need to access a value, it is already computed, greatly improving query execution performance.
• Create indexes on object columns and REF columns. Methods that describe objects can be used as functions on which to build indexes. For example, you can use the MAP method to build indexes on an object type column.
• Create more powerful sorts. You can perform case-insensitive sorts with the UPPER and LOWER functions, descending order sorts with the DESCkeyword, and linguistic-based sorts with the NLSSORT function.

Another function-based index calls the object method distance_from_equator for each city in the table. The method is applied to the object column Reg_Obj. A query could use this index to quickly find cities that are more than 1000 miles from the equator:

CREATE INDEX Distance_index 
ON Weatherdata_tab (Distance_from_equator (Reg_obj));

SELECT * FROM Weatherdata_tab 
WHERE (Distance_from_equator (Reg_Obj)) > '1000';

Another index stores the temperature delta and the maximum temperature. The result of the delta is sorted in descending order. A query could use this index to quickly find table rows where the temperature delta is less than 20 and the maximum temperature is greater than 75.

CREATE INDEX compare_index 
ON Weatherdata_tab ((Maxtemp - Mintemp) DESC, Maxtemp);

SELECT * FROM Weatherdata_tab
WHERE ((Maxtemp - Mintemp) < '20' AND Maxtemp > '75');

Examples of Function-Based Indexes

Example: Function-Based Index for Case-Insensitive Searches

The following command allows faster case-insensitive searches in table EMP_TAB.

CREATE INDEX Idx ON Emp_tab (UPPER(Ename));

The SELECT command uses the function-based index on UPPER(e_name) to return all of the employees with name like :KEYCOL.

SELECT * FROM Emp_tab WHERE UPPER(Ename) like :KEYCOL;

Example: Precomputing Arithmetic Expressions with a Function-Based Index

The following command computes a value for each row using columns A, B, and C, and stores the results in the index.

CREATE INDEX Idx ON Fbi_tab (A + B * (C - 1), A, B);

The SELECT statement can either use index range scan (since the expression is a prefix of index IDX) or index fast full scan (which may be preferable if the index has specified a high parallel degree).

SELECT a FROM Fbi_tab WHERE A + B * (C - 1) < 100;

Example: Function-Based Index for Language-Dependent Sorting

This example demonstrates how a function-based index can be used to sort based on the collation order for a national language. The NLSSORT function returns a sort key for each name, using the collation sequence GERMAN.

CREATE INDEX Nls_index 
    ON Nls_tab (NLSSORT(Name, 'NLS_SORT = German'));

The SELECT statement selects all of the contents of the table and orders it by NAME. The rows are ordered using the German collation sequence. The Globalization Support parameters are not needed in theSELECT statement, because in a German session, NLS_SORT is set to German and NLS_COMP is set to ANSI.

SELECT * FROM Nls_tab WHERE Name IS NOT NULL
    ORDER BY Name;

Restrictions for Function-Based Indexes

Note the following restrictions for function-based indexes:

• Only cost-based optimization can use function-based indexes. Remember to call DBMS_STATS.GATHER_TABLE_STATISTICS orDBMS_STATS.GATHER_SCHEMA_STATISTICS, for the function-based index to be effective.
• Any top-level or package-level PL/SQL functions that are used in the index expression must be declared as DETERMINISTIC. That is, they always return the same result given the same input, for example, the UPPER function. You must ensure that the subprogram really is deterministic, because Oracle Database does not check that the assertion is true.
  The following semantic rules demonstrate how to use the keyword DETERMINISTIC:
  • You can declare a top level subprogram as DETERMINISTIC.
  • You can declare a PACKAGE level subprogram as DETERMINISTIC in the PACKAGE specification but not in the PACKAGE BODY. Errors are raised ifDETERMINISTIC is used inside a PACKAGE BODY.
  • You can declare a private subprogram (declared inside another subprogram or a PACKAGE BODY) as DETERMINISTIC.
  • A DETERMINISTIC subprogram can call another subprogram whether the called program is declared as DETERMINISTIC or not.
• If you change the semantics of a DETERMINISTIC function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.
• Expressions in a function-based index cannot contain any aggregate functions. The expressions should reference only columns in a row in the table.
• You must analyze the table or index before the index is used.
• Bitmap optimizations cannot use descending indexes.
• Function-based indexes are not used when OR-expansion is done.
• The index function cannot be marked NOT NULL. To avoid a full table scan, you must ensure that the query cannot fetch null values.
• Function-based indexes cannot use expressions that return VARCHAR2 or RAW data types of unknown length from PL/SQL functions. A workaround is to limit the size of the function's output by indexing a substring of known length:
  
  

  -- INITIALS() might return 1 letter, 2 letters, 3 letters, and so on.
  -- We limit the return value to 10 characters for purposes of the index.
  CREATE INDEX func_substr_index ON
    emp_tab(substr(initials(ename),1,10);
  
  -- Call SUBSTR both when creating the index and when referencing
  -- the function in queries.
  SELECT SUBSTR(initials(ename),1,10) FROM emp_tab;