Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Function based index

    I have a query which is running really slow and want to know if I can use a function based index on ACC_DT. I have few indexes on the other columns in this table but its not helping in increasing the performance
    Select ..
    from..
    where...
    ...
    AND ( ( P1.YR ) = '1990' AND
    ( TO_CHAR(P1.act_dt,'MM') ) = '12' ) and.....
    group by
    ..
    ..

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    First:
    is p1.yr a numeric column or a character column? If it is a numeric column (which it ought to be as you are storing a number in it) than drop the quotes around 12. It will prevent the usage of an index and slow down the query because Oracle needs to do a type conversion.

    Second: you would need to create the index on TO_CHAR(act_dt,'MM'), not on act_dt itself, e.g
    Code:
    CREATE INDEX idx_act_dt ON p1 ( TO_CHAR(act_dt,'MM') )
    You could also try using extract(month from p1.act_dt) = 12 instead, that might be quicker.

    I have few indexes on the other columns in this table but its not helping in increasing the performance
    Check your execution plan with EXPLAIN PLAN so that you know for sure where the performance is lost. That's the only way to know whether an index is used or not and why the query is slow.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Thanks so much Shammat. I have to run the explain plan and see if that helps.

Posting Permissions

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