I have spent hrs searching the net to have an response to this...
This is what I presently have:
select * from order_header oh where tran_date = sysdate-1
Thanks ahead of time.
AND oh.tran_date BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE) - 1/86400
Calling a function around the
tran_date means the optimizer will not have the ability to make use of an index (presuming one is available) connected by using it. Some databases, for example Oracle, support function based indexes which permit carrying out functions around the data to reduce impact such situations, but IME DBAs will not allow these. And To be sure - they are not really necessary in cases like this.
trunc(tran_date) = trunc(sysdate -1)
to_char(tran_date, 'yyyy-mm-dd') = to_char(sysdate-1, 'yyyy-mm-dd')
If you do not support future dated transactions then something similar to this may work:
AND oh.tran_date >= trunc(sysdate-1)