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
————————————————————————————

source :How to use Oracle Function-Based Index

You may also like...

Leave a Reply

Your email address will not be published.