Unable to use Function Index

[surajitmitra78]

Hi,

I have created table called my_table, inserted some values. Created a function index which calls UPPER() function. When i query the table with the UPPER() function, it should be using the function index but it is not using the same i.e., it is hitting a full scan on the said table. Please some one let me know where have i gone wrong.

Following is the script:

create table my_table ( empid number(10), name varchar2(20) );

insert into my_table values (1,'John');
insert into my_table values (2,'Mack');
insert into my_table values (3,'Sura');
insert into my_table values (4,'Anil');
insert into my_table values (5,'Rasp');
commit;

CREATE INDEX idx_case_ins ON my_table(UPPER(name));

SELECT * FROM my_table WHERE UPPER(name) = 'SURA';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MY_TABLE'

[DaPi]

a) Probably FTS is the fastest way to execute the query on such a small table.

b) From the manual: "To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. In both cases, the table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index. In addition, in order for Oracle to use function-based indexes in queries, the QUERY_REWRITE_ENABLED parameter must be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to TRUSTED." Did you do all that?

c) Tables & indexes ANALYZE'd ?