1

I need to get all the rows where its 'time' field is equal to sysdate. The thing is I just want to compare dd/mm/yyyy and as my fields are in the date format I'm using 

select *  from myTable where trunc(timefield) = trunc(sysdate)

If there's only one field which is truncated everything works fine, but when I want to execute that request it takes too many times (> 10 minutes and still running).

Is there a way to bypass that?

Improve this question
0

1 Answer 1

Reset to default
4

If you want an index to be used, you should remove the function from the column. You can rewrite the condition (which removes the time part from the two datetimes): 

where trunc(timefield) = trunc(sysdate)

to the equivalent (where the function is applied only to sysdate and not the column):

where (timefield >= trunc(sysdate) and timefield < trunc(sysdate) + 1)

This way, the condition is "sargable" and an index on timefield can be used. With the previous condition, a table scan (or a full index scan) was required.

Improve this answer
3
  • +1 for 'where timefield >= trunc(sysdate) and timefield < trunc(sysdate)+1'
    – Md Haidar Ali Khan
    Commented Oct 26, 2015 at 11:37
  • The point is that the table is really really big so the index is ineffective for trunc function, equivalent is pretty good
    – So4ne
    Commented Oct 26, 2015 at 11:57
  • The same behavior happens in PostgreSQL 10.3
    – deFreitas
    Commented Mar 30, 2018 at 4:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.