Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Red face Unanswered: Oracle Index Issue

    Good Morning,

    I am trying to create a report using SQL Statement. In Where clause, I am using the following statement:

    WHERE TO_DATE(TO_CHAR(TABLE_NAME.VALUE_DATE,'dd-mon-yyyy')) > MyDate.

    When I use TO_DATE(TO_CHAR , Oracle doesn't use index built on VALUE_DATE field and my report becomes unacceptably slow. Without TO_DATE(TO_CHAR it is 3 seconds and with TO_DATE(TO_CHAR it is 5 minutes. Due to reporting tool (Business Object) constraint, I have to use this "TO_DATE(TO_CHAR" thing. Is there any way I can "TO_DATE(TO_CHAR" and index on field togather?

    Any help will be highly appreciated.

    Regards,
    mr_roomi

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Would it help if you did the formatting on MyDate instead of on the database value?
    Like:
    WHERE TABLE_NAME.VALUE_DATE > TO_DATE(TO_CHAR(MyDate), '<MyFormat>')

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there any way I can "TO_DATE(TO_CHAR" and index on field togather?
    NO, when a function is applied to a column, the index is never used.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Trunc

    Hi,

    I used Trunc function and it seems to work.
    mr_roomi

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    Why Trunc at all? If Trunc(date_value) > MyDate, then date_value is also > Mydate.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You might try with function-based indexes (this is a link returned by Google; explains what they are and gives a few examples). Also, read more about this feature in Oracle Documentation (search for "function-based").

  7. #7
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Helpful Link

    Hi LittleFoot,

    Link, provided by you, really help me to solve my problem with less effort.

    Thanks for great help.

    Khurram
    mr_roomi

Posting Permissions

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