Unable to use Function Index
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'