4 Using Indexes in Database Applications

Advantages of Function-Based Indexes

A function-based index has these advantages:

• A function-based index increases the number of situations where the database can perform an index range scan (described in Oracle Database Concepts) instead of a full index scan (described in Oracle Database Concepts).

  An index range scan typically has a fast response time when the WHERE clause selects fewer than 15% of the rows of a large table. The optimizer can more accurately estimate how many rows an expression selects if the expression is materialized in a function-based index.

  Oracle Database represents the index expression as a virtual column, on which the ANALYZE statement (described in Oracle Database SQL Language Reference) can build a histogram.

• A function-based index precomputes and stores the value of an expression.

  Queries can get the value of the expression from the index instead of computing it. The more queries that need the value, and the more computationally intensive the index expression, the more the index improves application performance. (See Example 4-1.)

• You can create a function-based index on an object column or REF column.

  The index expression can be the invocation of a method that returns an object type. For more information, see Oracle Database Object-Relational Developer's Guide. (See Example 4-2 and the example in Oracle Database SQL Language Reference.)

• A function-based index lets you perform more powerful sorts.

  The index expression can invoke the SQL functions UPPER and LOWER for case-insensitive sorts (as in Example 4-3) and the SQL function NLSSORT for linguistic-based sorts (as in Example 4-4).

Disadvantages of Function-Based Indexes

A function-based index has these disadvantages:

• The optimizer can use a function-based index only for cost-based optimization, not for rule-based optimization.

  The cost-based optimizer uses statistics stored in the dictionary. To gather statistics for a function-based index, invoke either DBMS_STATS.GATHER_TABLE_STATS (described in Oracle Database PL/SQL Packages and Types Reference) or DBMS_STATS.GATHER_SCHEMA_STATS (described in Oracle Database PL/SQL Packages and Types Reference).

• The database does not use a function-based index until you analyze the index itself and the table on which it is defined.

  To analyze the index and the table on which the index is defined, invoke either DBMS_STATS.GATHER_TABLE_STATS or DBMS_STATS.GATHER_SCHEMA_STATS.

• The database does not use function-based indexes when doing OR expansion.

• You must ensure that any schema-level or package-level PL/SQL function that the index expression invokes is deterministic (that is, that the function always return the same result for the same input).

  You must declare the function as DETERMINISTIC, but because Oracle Database does not check this assertion, you must ensure that the function really is 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.

• If the index expression is a function invocation, then the function return type cannot be constrained.

  Because you cannot constrain the function return type with NOT NULL, you must ensure that the query that uses the index cannot fetch NULL values. Otherwise, the database performs a full table scan.

• The index expression cannot invoke an aggregate function (described in Oracle Database SQL Language Reference).

• A bitmapped function-based index cannot be a descending index (see Oracle Database SQL Language Reference).

• The data type of the index expression cannot be VARCHAR2, RAW, LONGRAW, or a PL/SQL data type of unknown length.

  That is, you cannot index an expression of unknown length. However, you can index a known-length substring of that expression. For example:

  CREATE OR REPLACE FUNCTION initials (
    name  IN VARCHAR2
  ) RETURN VARCHAR2
    DETERMINISTIC
  IS
  BEGIN
    RETURN('A. J.');
  END;
  /
  
  /* Invoke SUBSTR both when creating index and when referencing
     function in queries. */
   
  CREATE INDEX func_substr_index ON
  EMPLOYEES(SUBSTR(initials(FIRST_NAME),1,10));
   
  SELECT SUBSTR(initials(FIRST_NAME),1,10) FROM EMPLOYEES;
  

Examples of Function-Based Indexes

Examples:

• Example 4-1, "Function-Based Index for Precomputing Arithmetic Expression"

• Example 4-2, "Function-Based Indexes on Object Column"

• Example 4-3, "Function-Based Index for Faster Case-Insensitive Searches"

• Example 4-4, "Function-Based Index for Language-Dependent Sorting"

Example 4-1 creates a table with columns a, b, and c; creates an index on the table, and then queries the table. The index is a composite index on three columns: a virtual column that represents the expression a+b*(c-1), column a, and column b. The query uses the indexed expression in its WHERE clause; therefore, it can use an index range scan instead of a full index scan.

DROP TABLE Fbi_tab;
CREATE TABLE Fbi_tab (
  a INTEGER, 
  b INTEGER, 
  c INTEGER
);

CREATE INDEX Idx ON Fbi_tab (a+b*(c-1), a, b);

SELECT a FROM Fbi_tab WHERE a+b*(c-1) < 100;

In Example 4-2, assume that the object type Reg_obj has been defined, and that it stores information about a city. The example creates a table whose first column has type Reg_obj, a deterministic function with a parameter of type Reg_obj, and two function-based indexes that invoke the function. The first query uses the first index to quickly find cities further than 1000 miles from the equator. The second query uses the second index (which is composite) to quickly find cities where the temperature delta is less than 20 and the maximum temperature is greater than 75. (The table is not populated for the example, so the queries return no rows.)

DROP TABLE Weatherdata_tab;
CREATE TABLE Weatherdata_tab (
  Reg_obj INTEGER,
  Maxtemp INTEGER,
  Mintemp INTEGER
);
 

CREATE OR REPLACE FUNCTION Distance_from_equator (
  Reg_obj IN INTEGER
) RETURN INTEGER
  DETERMINISTIC
IS
BEGIN
  RETURN(3000);
END;
/
 

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

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

no rows selected
 

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

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

no rows selected

Example 4-3 creates an index that allows faster case-insensitive searches in the EMPLOYEES table and then uses the index expression in a query.

CREATE INDEX emp_lastname ON EMPLOYEES (UPPER(LAST_NAME));

SELECT first_name, last_name
FROM EMPLOYEES
WHERE UPPER(LAST_NAME) LIKE 'J%S_N';

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Charles              Johnson
 
1 row selected.

Example 4-4 creates a table with one column, NAME, and a function-based index to sort that column using the collation sequence GERMAN, and then selects all columns of the table, ordering them by NAME. Because the query can use the index, the query is faster. (Assume that the query is run in a German session, where NLS_SORT is set to GERMAN and NLS_COMP is set to ANSI. Otherwise, the query would have to specify the values of these Globalization Support parameters.)

DROP TABLE nls_tab;
CREATE TABLE nls_tab (NAME VARCHAR2(80));

CREATE INDEX nls_index
  ON nls_tab (NLSSORT(NAME, 'NLS_SORT = GERMAN'));

SELECT * FROM nls_tab
WHERE NAME IS NOT NULL
ORDER BY NAME;