I've spent hours searching the web for an answer to this question...
Here's what I currently have:
select *
from order_header oh
where tran_date = sysdate-1
Thanks in advance.
views:
447answers:
4If, as I presume, there's an index on tran_date on which this query relies, calling a funtion on the indexed column like this will decimate the performance of the query.
ninesided
2009-10-15 19:46:34
then rexem's method will be helpful.
Henry Gao
2009-10-15 19:48:31
In that case you can add a function based index:create index index_name on table_name(trunc(tran_date));
Robert Merkwürdigeliebe
2009-10-15 19:49:14
@Robert: IME, most DBAs won't let you. And I have to agree, because it's not really necessary.
OMG Ponies
2009-10-15 19:52:36
Henry is right : rexem's solution is probably better (less impact) than adding a functio based index...
Robert Merkwürdigeliebe
2009-10-15 19:53:37
A:
to_char(tran_date, 'yyyy-mm-dd') = to_char(sysdate-1, 'yyyy-mm-dd')
Pablo Santa Cruz
2009-10-15 19:40:44
Oracle supports a wide range of operators directly on dates, without having to resort to doing string comparisons.
Jeffrey Kemp
2010-02-23 07:39:19
+10
A:
Use:
AND oh.tran_date BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE) - 1/86400
Reference: TRUNC
Calling a function on the tran_date means the optimizer won't be able to use an index (assuming one exists) associated with it. Some databases, such as Oracle, support function based indexes which allow for performing functions on the data to minimize impact in such situations, but IME DBAs won't allow these. And I agree - they aren't really necessary in this instance.
OMG Ponies
2009-10-15 19:45:27
One small caveat-- you may need to subtract 1 second from the upper bound. Otherwise, if you have a TRAN_DATE which is today at midnight, it will be returned in the query for rows inserted yesterday. I wouldn't expect that to be the desired behavior.
Justin Cave
2009-10-16 05:57:45
A:
If you don't support future dated transactions then something like this might work:
AND oh.tran_date >= trunc(sysdate-1)
ninesided
2009-10-15 19:52:35