This discussion is archived
2 Replies Latest reply: Jun 19, 2012 10:34 AM by rp0428 RSS

Index creation on Function

858551 Newbie
Currently Being Moderated
I have a query where I am using the to_date() function in the where clause, due to that the index is not being utilized as it is a function and preventing the optimizer to use indexes on that column.

Is it good to create a function based index?

Regards,
Abhishek

Edited by: f!do on Jun 19, 2012 2:18 AM
  • 1. Re: Index creation on Function
    nkswapnil Newbie
    Currently Being Moderated
    Hi abhishek,

    You cant judge it about index, its depends upon requirement ..if your report required based on date, and this report is frequently used, then its always good to create function based index on column.
  • 2. Re: Index creation on Function
    rp0428 Guru
    Currently Being Moderated
    >
    I have a query where I am using the to_date() function in the where clause, due to that the index is not being utilized as it is a function and preventing the optimizer to use indexes on that column.

    Is it good to create a function based index?
    >
    Wrong forum! This question belongs on the SQL and PL/SQL forum.

    Depending on the selectivity of the index Oracle may not use it even if you have an index. You will have to test it.

    If you are using Oracle 11 you could add a virtual column that performs the TO_DATE and then modify your query to use the new virtual column. See this Oracle-base article for an example of using virtual columns.
    http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php

    If the above does not answer your question

    1. Create a new thread on the SQL and PL/SQL forum

    2. Edit this question to add a link to the new question on the other forum

    3. Mark this question ANSWERED so people will follow up in the other forum

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points