Oracle 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
- Build Regular Index
- Build Function-Based Index
- Concatenated Columns
- Build Test Table
- Build Regular Index
- Build Function-Based Index
- Concatenated Columns
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 mydata (
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 mydata
VALUES (cur_rec, 'Ashwani' || cur_rec, 'Doe', 'M', SYSDATE);
ELSE
INSERT INTO mydata
VALUES (cur_rec, 'Richard' || cur_rec, 'Doe', 'F', SYSDATE);
END IF;
COMMIT;
END LOOP;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'mydata', 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 mydata
WHERE UPPER(first_name) = 'ASHWANI2';
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| MYDATA | 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 mydata (first_name);
EXEC DBMS_STATS.gather_table_stats(USER, 'mydata', cascade => TRUE);
SET AUTOTRACE ON
SELECT *
FROM mydata
WHERE UPPER(first_name) = 'ASHWANI2';
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| MYDATA | 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 mydata (UPPER(first_name));
EXEC DBMS_STATS.gather_table_stats(USER, 'mydata', 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 mydata
WHERE UPPER(first_name) = 'ASHWANI2';
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| MYDATA | 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 mydata (gender, UPPER(first_name), dob);
EXEC DBMS_STATS.gather_table_stats(USER, 'mydata', cascade => TRUE);
SET AUTOTRACE ON
SELECT *
FROM mydata
WHERE gender = 'M'
AND UPPER(first_name) = 'ASHWANI2';
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| MYDATA | 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.
