Function Based Indexes

Posted By Sagar Patil

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
* Build Regular Index
* Build Function Based Index

Build Test Table
First we build a test table and populate it with enough data so that use of an index would be advantageous:

Code:
CREATE TABLE user_data (
id          NUMBER(10)    NOT NULL,
first_name  VARCHAR2(40)  NOT NULL,
last_name   VARCHAR2(40)  NOT NULL);

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’);
    ELSE
      INSERT INTO user_data
      VALUES (cur_rec, ‘Jayne’ || cur_rec, ‘Doe’);
    END IF;
    COMMIT;
  END LOOP;
END;
/

ANALYZE TABLE user_data COMPUTE STATISTICS;

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
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
[b]   1    0   TABLE ACCESS (FULL) OF ‘USER_DATA’ (Cost=1 Card=1 Bytes=10)[/b]

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);
ANALYZE TABLE user_data COMPUTE STATISTICS;

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  Upper(first_name) = ‘JOHN2’;

Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
   1    0   TABLE ACCESS (FULL) OF ‘USER_DATA’ (Cost=1 Card=1 Bytes=10)

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));
ANALYZE TABLE user_data COMPUTE STATISTICS;

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
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘USER_DATA’ (Cost=2 Card=1 Bytes=14)

[b]   2    1     INDEX (RANGE SCAN) OF ‘FIRST_NAME_IDX’ (NON-UNIQUE) (Cost=1 Card=1)[/b]

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. These parameters may be set in the Init.ora file if they are needed permanently.

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu