Function Based Indexes, Please Help

Hi Guys,

We have created function based indexes and in the queries the FBI is not getting used, unless specified with a hint. The init.ora parameters QUERY_REWRITE_ENABLED = TRUE & QUERY_REWRITE_INTEGRITY = TRUSTED is set. Can someone tell me what else needs to be done.

Thanks in Advance.

2 How to create a function based index

Step 1: Set QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = TRUSTED
TIMED_STATISTICS = TRUE (Optional)

Step 2: Create TABLE TAMIL_TEST ( id number , name varchar2(20),
state varchar2(2)) ;

Step 3 Populate 1 Million rows data in the table. The simple way is to use PL/SQL.
FOR K IN 1..1000000 LOOP
INSERT INTO TAMIL_TEST
VALUES ( K, ‘KANNAN’||TO_CHAR(K), ‘GA’);
END LOOP;
COMMIT;
END;
/
Step 4 Update the state column with null value for some of the rows.
UPDATE TAMIL_TEST SET STATE = NULL
WHERE ID BETWEEN 800000 AND 800200 ;
COMMIT;

Step 5 CREATE INDEX TEST_IDX ON TAMIL_TEST NVL(STATE,’X’) TABLESPACE USERS ;

Step 6 Analyze table:
ANALYZE TABLE TAMIL_TEST COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXED COLUMNS
FOR ALL INDEXES;
Note: Analyzing table must be done after the index is created, otherwise function-based index will not work.

Step 7 Set autotrace on with explain
Run a query:
Select * from tamil_test where nvl(state,’x’) = ‘x’ ;
The explain plan statement will show that the index is used.

Hi Tamilselvan,

Thanks for your reply. I already created the function based indexes and anlyzed the tables. but in the select statement it is not using the function based indexes, instead if I use a index hint it uses the function based indexes. Am I doing something wrong.

Thanks.

The problem with Function based index is :
You must FIRST create an index and then analyze the table as shown above, otherwise the index will not be used.

Hi Tamilselvan,

I created the Index then analyzed the table. I have multiple databases, one on w2k which is using function based indexes. second one on Solaris which is not using function based indexes. What I found is when FIRST_ROWS pr COST hint is given in the queery it is using the index.

Thanks,