Oracle-L: RE: SQL statement help - passing sysdate

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 11 Apr 2003 13:28:50 -0800
Message-ID: <F001.0057FE02.20030411132850@fatcity.com>

By the way, this thing will go a lot faster (if there's an index on a.order_date) if you re-code the WHERE clause to take the trunc() off the a.order_date column name. You can probably do this easily enough with a BETWEEN predicate, to specify that any order_date existing between two timestamps (e.g., one midnight to the next) should qualify.

This type of date-range query is a common performance killer that we see in the field. The trunc(a.order_date) prohibits the use of an index on a.order_date. You *could* use a function-based index on the whole trunc() expression, but it's a waste of space and DML workload, considering that the problem is easy enough to fix with just a little work on the SQL.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic 101 in London, Reykjavik, Ottawa, Denver, Sydney
- Visit www.hotsos.com for schedule details...
- IOUG-A Live 2003, Orlando, 10am Monday 28 April: "Oracle Operational

Timing Data"

-----Original Message-----
Sent: Friday, April 11, 2003 1:48 PM
To: Multiple recipients of list ORACLE-L

Answered my own question!

Thank you all.

SQL> select a.cust_num, count(b.ord_id) from ord a, ord_l b where a.ord_id=b.ord_id and
a.cust_num='2' and
trunc(a.order_date)=trunc(sysdate)-1

group by a.cust_num;
                    ^^^^^

CUST_NUM COUNT(B.ORD_ID)
---------- ---------------

2                      574

SQL> -----Original Message-----
Sent: April 11, 2003 9:31 AM
To: 'ORACLE-L_at_fatcity.com'

Could someone guide me as to what is wrong with the SQL statement below? I'm trying to get a total number of order lines for yesterday's date in a report.

I could run it at 11:59 PM that day and just use sysdate, but there is a possibility that a couple of orders coming in at that time would not be included in the report.

The SQL statement works when I explicitly state yesterday's date but in a PL/SQL script that will be run daily from cron and e-mailed to people, I want to be able to pass this value dynamically.

SQL> select a.cust_num, count(b.ord_id) from ord a, ord_l b where a.ord_id=b.ord_id and a.cust_num='11' and trunc(a.order_date)='10-APR-03' group by a.cust_num;

CUST_NUM COUNT(B.ORD_ID)
---------- ---------------

11                     880

SQL> select sysdate - 1 from dual;

SYSDATE-1

10-APR-03 SQL> select a.cust_num, count(b.ord_id) from ord a, ord_l b where a.ord_id=b.ord_id and a.cust_num='11' and trunc(a.order_date)=(select sysdate - 1 from dual) group by a.cust_num;

no rows selected

Thanks in advance.
Saira

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Saira Somani
INET: saira_somani_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Cary Millsap
INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

RE: SQL statement help - passing sysdate - 1