8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Function-Based Indexes
Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function-Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data. When a query is passed to the server that could benefit from that index, the query is rewritten to allow the index to be used. The following code samples give an example of the use of Function-Based Indexes.
Build Test Table
First we build a test table and populate it with enough data so that use of an index would be advantageous.
CREATE TABLE user_data ( id NUMBER(10) NOT NULL, first_name VARCHAR2(40) NOT NULL, last_name VARCHAR2(40) NOT NULL, gender VARCHAR2(1), dob DATE ); BEGIN FOR cur_rec IN 1 .. 2000 LOOP IF MOD(cur_rec, 2) = 0 THEN INSERT INTO user_data VALUES (cur_rec, 'John' || cur_rec, 'Doe', 'M', SYSDATE); ELSE INSERT INTO user_data VALUES (cur_rec, 'Jayne' || cur_rec, 'Doe', 'F', SYSDATE); END IF; COMMIT; END LOOP; END; / EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE);
At this point the table is not indexed so we would expect a full table scan for any query.
SET AUTOTRACE ON SELECT * FROM user_data WHERE UPPER(first_name) = 'JOHN2'; Execution Plan ---------------------------------------------------------- Plan hash value: 2489064024 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 540 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| USER_DATA | 20 | 540 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------------
Build Regular Index
If we now create a regular index on the FIRST_NAME column we see that the index is not used.
CREATE INDEX first_name_idx ON user_data (first_name); EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE); SET AUTOTRACE ON SELECT * FROM user_data WHERE UPPER(first_name) = 'JOHN2'; Execution Plan ---------------------------------------------------------- Plan hash value: 2489064024 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 540 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| USER_DATA | 20 | 540 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------------
Build Function-Based Index
If we now replace the regular index with a function-based index on the FIRST_NAME column we see that the index is used.
DROP INDEX first_name_idx; CREATE INDEX first_name_idx ON user_data (UPPER(first_name)); EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE); -- Later releases set these by default. ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE; SET AUTOTRACE ON SELECT * FROM user_data WHERE UPPER(first_name) = 'JOHN2'; Execution Plan ---------------------------------------------------------- Plan hash value: 1309354431 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USER_DATA | 1 | 36 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | FIRST_NAME_IDX | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------
The QUERY_REWRITE_INTEGRITY
and QUERY_REWRITE_ENABLED
parameters must be set or the server will not be able to
rewrite the queries, and will therefore not be able to use the new index. Later releases have them enabled by default.
Concatenated Columns
This method works for concatenated indexes also.
DROP INDEX first_name_idx; CREATE INDEX first_name_idx ON user_data (gender, UPPER(first_name), dob); EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE); SET AUTOTRACE ON SELECT * FROM user_data WHERE gender = 'M' AND UPPER(first_name) = 'JOHN2'; Execution Plan ---------------------------------------------------------- Plan hash value: 1309354431 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USER_DATA | 1 | 36 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | FIRST_NAME_IDX | 1 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------
Remember, function-based indexes require more effort to maintain than regular indexes, so having concatenated indexes in this manner may increase the incidence of index maintenance compared to a function-based index on a single column.
For more information see:
Hope this helps. Regards Tim...