will >= sysdate-10/1440 use the index?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: will >= sysdate-10/1440 use the index?

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I have the following select statement:
    select distinct trans_id, 241
    from trans
    where schedule_date >= sysdate-10/1440
    and schedule_date <= sysdate+1/24

    The table trans has an index on schedule date, but it doesn't use the index.

    I am sure it won't use the index due to the calulation after sysdate, am I right? Or should it be using the index?Thanks.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, you are wrong. Calculation is not made on the indexed column, so nothing is preventing the usage of the index. But I guess optimizer is choosing not to use index for other reasons.

    Note that if you would rewrite your condition to the following, this would indeed prevent the usage of the index:

    ....
    where schedule_date+10/1440 >= sysdate
    and schedule_date-1/24 <= sysdate
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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