Thursday, April 23, 2009

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

No comments:

Post a Comment