When Oracle Function Based Indexes is Useful
Step 1: Create a test table
First we create a test table and populate it with enough data so that use of an index would be advantageous.
CREATE TABLE test (
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 test
VALUES (cur_rec, 'Rasel' || cur_rec, 'Doe', 'M', SYSDATE);
ELSE
INSERT INTO test
VALUES (cur_rec, 'Said' || cur_rec, 'Doe', 'F', SYSDATE);
END IF;
COMMIT;
END LOOP;
END;
/
Step2: Gather statistics of the table
EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade => TRUE);
Step3: At this point the table is not indexed so we would expect a full table scan for any query.
SET AUTOTRACE ON
SELECT *
FROM test
WHERE UPPER(first_name) = 'RASEL2';
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| test | 20 | 540 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Build Regular Index
Step4: Now we create a regular index on the FIRST_NAME column we see that the index is not used.
CREATE INDEX first_name_idx ON test (first_name);
EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade => TRUE);
SET AUTOTRACE ON
SELECT *
FROM test
WHERE UPPER(first_name) = 'RASEL2';
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| test | 20 | 540 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Step5: Now we 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 test (UPPER(first_name));
EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade => TRUE);
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE ON
SELECT *
FROM test
WHERE UPPER(first_name) = 'RASEL2';
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| test | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FIRST_NAME_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Note: 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. Later releases have them enabled by default.
Step6: This method works for concatenated indexes also.
DROP INDEX first_name_idx;
CREATE INDEX first_name_idx ON test (gender, UPPER(first_name), dob);
EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade => TRUE);
SET AUTOTRACE ON
SELECT *
FROM test
WHERE gender = 'M'
AND UPPER(first_name) = 'RASEL2';
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| test | 1 | 36 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FIRST_NAME_IDX | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Note: 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.
Ref: http://www.oracle-base.com/articles/8i/function-based-indexes.php
