If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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 ??

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:

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.

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

Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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.