funcation based composite index

Hi All,

I have created a function based composite index using substr function. The query doesn't use the index. I am not sure wether substr can be used in a function based index and can a function based index be composite ??

I would really appreciate your help.

Thanks,

solution

Hi, 25th May 2001 21:32 hrs chennai

From an Article
-----------------
With standard indexing, if a column is involved in a function (for example, substr, trunc, etc.), the index is ignored. This results in full table scans and decreased performance. In 8i you have the ability to index a column with a function. This may be a built-in function or a user created function. The only caveat is the function must be deterministic (that is, given the same input it will always produce the same output). To enable this feature, the index owner must have the query rewrite the privilege, and set the following in your init.ora:
query_rewrite_integrity=trusted,
=>query_rewrite_enabled=true,
=>and compatible=8.1.0.

This done, let’s say you commonly query col1 of table1 with the statement:

select col1,col2,col3 from table1
=>where to_char(col1,’MM-DD-YYYY’)
=>=trunc(sysdate)

The function to_char would cause any indexes on col1 to be ignored resulting in a full table scan in your explain plan. Now you can create an index:

create index table1_2_char on table1
=>(to_char(col1,’MM-DD-YYYY’))
tablespace .....
storage(......)

After analyzing the table, an explain plan would look like Table A. The rule regarding data cardinality and index versus full scans still holds, but the performance gains you can realize can be amazing.

Table A: An explain plan

Query Cost Cardinality
Select statement choos 15 720
table access by index rowid table1 15 720
index range scan table1_idx analyzed 4 720

Note: it has been discovered that an index involving the trunc function will never be used, but a deterministic user created function that returns the trunc will work fine. This is an Oracle bug that they are aware of and plan to fix.

Cheers

Padmam

Thank You Padmam for your suggestions. It works.

Thanks

Could you verify whether this is true:

In order to use function based index as specified in this thread, the table will have to be analyzed first, and analyzed regularly. So function based index carries certain amount of maintenance requirement.