Unable to use Function Index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Unable to use Function Index

  1. #1
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67

    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'
    Regards,
    Surajit K Mitra

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width