DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: funcation based composite index

  1. #1
    Join Date
    Nov 2000
    Posts
    79
    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,

  2. #2
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    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
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  3. #3
    Join Date
    Nov 2000
    Posts
    79
    Thank You Padmam for your suggestions. It works.


    Thanks




  4. #4
    Join Date
    Oct 2000
    Posts
    76
    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.
    J.T.

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