| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: problem with trunc()
Here's the query and execution plan. Like I said, it took 6-7 hours just to get that without executing. Any insight appeciated. Remember: I know the workaround (avoid trunc). I'd just like to know *why* it doesn't work.
Some of the values were dummied up ('XXXX' etc.). The actual query is from a Perl/DBI script that uses placeholders, etc. I modified it slightly to feed it to SQL*Plus for testing. It's the trunc() in the BETWEEN clause that causes problems, and as noted earlier, changing that to a single trunc with any comparison gives me 1-2 minute results versus 7 hours. Also, changing the BETWEEN to >= date AND <= data is the same as the BETWEEN -- it's the existence of the 2 trunc's that does it.
Here's the bizzaro footnote of the day: There's another trunc() used in the in-line view. Removing that makes things a little slower (5-6 minutes versus 1-2). Very strange. One has to think there are optimization bugs in Oracle related to trunc ...
1 SELECT 'XXXX'
"MERCHANT
ID",
2 it.transaction_type
"TYPE",
3 to_char(it.transaction_date,'MMDDYYYY HH24:MI:SS')
"DATE",
4 substr(co.orso_code,4,length(co.orso_code) - 3)
"SOURCE ID
",
5 it.order_id "ORDERID"
8 to_char(round((oi.total/oi.qty_ordered), 2), '999999.00') 9 "UNITPRIC
13 FROM item_transactions it, customer_order co, orso_store,
14 (SELECT
15 order_item.order_id,
16 order_item.sku,
17 max(order_item.product_id) product_id,
18 SUM(nvl(order_item.quantity, 0) +
19 nvl(order_item.bo_quantity, 0)) qty_ordered,
20 SUM(order_item.total_amount) total
21 FROM order_item, customer_order
22 WHERE customer_order.order_id = order_item.order_id
23 AND trunc(customer_order.order_date) >= '14-FEB-01'
24 AND orso_type = 'BF'
25 GROUP BY order_item.order_id, order_item.sku) oi
26 WHERE orso_store.store_code = 'FOG'
27 AND trunc(it.transaction_date) BETWEEN
28 to_date('May-19-2001', 'Mon-dd-yyyy') AND
29 to_date('May-28-2001', 'Mon-dd-yyyy')
30 AND to_number(it.sku) > 99999 and to_number(it.sku) <
8000000
31 AND co.order_id = it.order_id 32 AND co.orso_type = 'BF' 33 AND substr(co.orso_code, 1, 2) = orso_store.store_number 34 AND oi.order_id = it.order_id 35 AND oi.sku = it.sku 36* ORDER BY it.transaction_date, it.order_id, product_id37
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2532 Card=1 Bytes=14
1)
1 0 SORT (ORDER BY) (Cost=2532 Card=1 Bytes=141)
2 1 NESTED LOOPS (Cost=2527 Card=1 Bytes=141)
3 2 NESTED LOOPS (Cost=2525 Card=1 Bytes=126)
4 3 NESTED LOOPS (Cost=153 Card=1 Bytes=43)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'ORSO_STORE' (Cos
t=1 Card=1 Bytes=18)
6 5 INDEX (RANGE SCAN) OF 'SYS_C0015465' (UNIQUE) (C
ost=1 Card=1)
7 4 TABLE ACCESS (FULL) OF 'ITEM_TRANSACTIONS' (Cost=1
52 Card=1 Bytes=25)
8 3 VIEW
9 8 SORT (GROUP BY) (Cost=2372 Card=1600 Bytes=59200)
10 9 NESTED LOOPS (Cost=2357 Card=1600 Bytes=59200)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER_ORD
ER' (Cost=998 Card=453 Bytes=7248)
12 11 INDEX (RANGE SCAN) OF 'CUSTOMER_ORDER_IDX4'
(NON-UNIQUE) (Cost=19 Card=453)
13 10 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
(Cost=3 Card=2277488 Bytes=47827248)
14 13 INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX2' (NON
-UNIQUE) (Cost=2 Card=2277488)
15 2 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER_ORDER' (Cos
t=2 Card=9060 Bytes=135900)
16 15 INDEX (UNIQUE SCAN) OF 'SYS_C001085' (UNIQUE) (Cost=
1 Card=9060)
Work Email: sapovitss_at_globalsports.com
Home Email: steves_at_delanet.com
Work Phone: 610-491-7087
Cell: 610-574-7706
Pager: 877-239-4003
> -----Original Message-----
> From: Steve Sapovits [SMTP:SapovitsS_at_globalsportsinc.com]
> Sent: Thursday, May 31, 2001 12:47 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: problem with trunc()
>
>
> Doing that now. The last time I tried it hung out there for
> over 5 hours and I had to kill it for other reasons. Isn't
> there a faster analysis tool?
>
> It seems to be an Oracle Catch-22 that analyzing queries that
> take too long takes too long ...
>
> ----
> Steve Sapovits
> Global Sports Interactive
> Work Email: sapovitss_at_globalsports.com
> Home Email: steves_at_delanet.com
> Work Phone: 610-491-7087
> Cell: 610-574-7706
> Pager: 877-239-4003
>
> > -----Original Message-----
> > From: Lisa Koivu [SMTP:lisa.koivu_at_efairfield.com]
> > Sent: Thursday, May 31, 2001 11:43 AM
> > To: 'ORACLE-L_at_fatcity.com'; 'SapovitsS_at_globalsportsinc.com'
> > Subject: RE: problem with trunc()
> >
> > Steve, we need to see you explain plan! Can you at least post an
> > autotrace?
> > it's possible you have wide range scans going on, even with an index.
> > Send us your autotrace, the query does not have to execute in order for
> > that to be done
> >
> > SET AUTOTRACE TRACEONLY EXPLAIN
> >
> > and execute the query. It will just give you the plan.
> > Lisa Koivu
> > Oracle Database Administrator
> > 954-935-4117
> >
> > The information in the electronic mail message is Cendant confidential
> and
> > may be legally privileged, it is intended solely for the addressee(s)
> > access to this internet electronic mail message by anyone else is
> > unauthorized. If you are not the intended recipient, any disclosure,
> > copying, distribution or any action taken or omitted to be taken in
> > reliance on it is prohibited and may be unlawful.
> >
> > The sender believes that this E-mail and any attachments were free of
> any
> > virus, worm, Trojan horse, and/or malicious code when sent. This message
> > and its attachments could have been infected during transmission. By
> > reading the message and opening any attachments, the recipient accepts
> > full responsibility for taking protective and remedial action about
> > viruses and other defects. Cendant Corporation or Affiliates are not
> > liable for any loss or damage arising in any way from this message or
> its
> > attachments.
> >
> >
> >
> > -----Original Message-----
> > From: Steve Sapovits [SMTP:SapovitsS_at_globalsportsinc.com]
> > Sent: Thursday, May 31, 2001 12:01 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: problem with trunc()
> >
> >
> > I have a query that contains these lines as part of the WHERE
> > clause:
> >
> > WHERE trunc(it.transaction_date) = to_date('May-19-2001',
> > 'Mon-dd-yyyy')
> >
> > This works fine -- the query returns in about a minute, which is
> > what
> > I'd expect for the table sizes, the rest of the joins, etc.
> >
> > Changing to the following causes the time to go to about 7 hours!
> >
> > WHERE trunc(it.transaction_date) BETWEEN
> > to_date('May-19-2001', 'Mon-dd-yyyy') AND
> > to_date('May-28-2001', 'Mon-dd-yyyy')
> >
> > Changing the BETWEEN to >= AND <= gives the same long results.
> >
> > What makes it go back to normal is dropping the trunc(), or
> > otherwise
> > rewriting it so there is no trunc(). So I have a workaround. But I'm
> > curious why there'd be such a huge difference. Running just the BETWEEN
>
> > piece by itself works fine.
> >
> > This is Oracle 8.1.6 on Solaris. I've tried indexing the
> > transaction_date
> > column both as transaction_date and trunc(transaction_date) but there's
> no
> >
> > difference. I've used both individual column indexes and combined with
> > other WHERE clause columns used.
> >
> > One web site I found said there's an 8i bug creating functional
> > indexes
> > using
> > trunc() but I have not verified that.
> >
> > I've started to analyze but SQL*Plus autotrace takes almost as long
> > to
> > return (7 hours) so it's not too useful at this point.
> >
> > ----
> > Steve Sapovits
> > Global Sports Interactive
> > Work Email: sapovitss_at_globalsports.com
> > Home Email: steves_at_delanet.com
> > Work Phone: 610-491-7087
> > Cell: 610-574-7706
> > Pager: 877-239-4003
> >
> > --
> > Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
> > --
> > Author: Steve Sapovits
> > INET: SapovitsS_at_globalsportsinc.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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.com
> --
> Author: Steve Sapovits
> INET: SapovitsS_at_globalsportsinc.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com -- Author: Steve Sapovits INET: SapovitsS_at_globalsportsinc.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).Received on Thu May 31 2001 - 20:52:23 CDT
 |
 |