I have a query which has the following WHERE clause (note that this is a simplification. In the actual case the second parameter would be passed from code. But the following explanation applies to when executing from sql developer).

WHERE O_DATE>= greatest(sysdate-1, sysdate - 30)

which takes about 100 seconds, whereas

WHERE O_DATE>= sysdate-1 takes very little time.

I would assume both to take the same amount of time because greatest(sysdate-1, sysdate - 30) should resolve to sysdate-1.

Can someone tell me why the difference in execution time? And a way to work around it?

To add more context I am using the logstash jdbc plugin and trying to use the sql_last_value


Look at the EXPLAIN PLAN and you will see the difference. Oracle uses a cost based optimizer that takes into account the actual values that are used in the query to come up with the optimal execution plan. I have not tested it in Oracle, but it is most likely that sysdate-1 is evaluated at compile/optimize time, and the optimizer is aware that your predicate only covers one day, and therefor it will make sense to use an index for example. When you 'hide' the value in a function, the optimizer has no way to tell what value you use, as the function is only evaluated at execute time. If the general density of the column suggests that the average value will return many rows, and will benefit from a full table scan for example, you will get a full scan even for 1 day's worth of data, which may be less optimal for the value you are using. As a rule of thumb - don't 'hide' predicate values from the optimizer by placing them in functions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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