Oracle Tips : How to use Oracle Function-Based Index (Example)
Oracle Tips : How to use Oracle Function-Based Index (Example)
Create test table for testing Function-Based Index
CREATE TABLE TEST_TABLE (
ID NUMBER(10) NOT NULL,
FNAME VARCHAR2(250) NOT NULL
);
Insert sample data into test table
INSERT INTO TEST_TABLE VALUES (1,’Ant’);
INSERT INTO TEST_TABLE VALUES (2,’Ball’);
INSERT INTO TEST_TABLE VALUES (3,’Cat’);
INSERT INTO TEST_TABLE VALUES (4,’Dog’);
INSERT INTO TEST_TABLE VALUES (5,’Egg’);
INSERT INTO TEST_TABLE VALUES (6,’Fan’);
COMMIT;
Create a regular index on the FNAME column
CREATE INDEX TEST_TABLE_FNAME_IDX ON TEST_TABLE (FNAME);
Check by Execution Plan . Index is still not used.
SET AUTOTRACE ON;
SELECT ID,FNAME
FROM TEST_TABLE
WHERE UPPER(FNAME) = ‘DOG’;
—————————————-
Id Operation Name
—————————————-
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL TEST_TABLE
—————————————-
Drop and create function-based index on the FNAME column
DROP INDEX TEST_TABLE_FNAME_IDX;
CREATE INDEX TEST_TABLE_FNAME_IDX ON TEST_TABLE (UPPER(FNAME));
These two alter session queries ensures that the new index is used
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE ON;
SELECT ID,FNAME
FROM TEST_TABLE
WHERE UPPER(FNAME) = ‘DOG’;
————————————————————————————
Id Operation Name Rows Bytes Cost
————————————————————————————
0 SELECT STATEMENT 1 8 2
1 TABLE ACCESS BY INDEX ROWID TEST_TABLE 1 8 2
* 2 INDEX RANGE SCAN TEST_TABLE_FNAME_IDX 1 1
————————————————————————————
