| 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()
Hi Steve,
Looking at your query - I'm wondering why you are trunc'ing both?
in your inline view, can't you get away with
and it.transaction_date between (to_date('05-19-01 00:00:00','mm-dd-yy hh24:mi:ss')) and (to_date('05-28-01 00:00:00','mm-dd-yy hh24:mi:ss'))
or 29th and 18th, whichever would provide you with the correct window?
Same with the customer_order.order_date > to_date('02-14-01' ... ) line.
Am I losing it? as long as it's a window and not equality, I don't see why you need to trunc the data. Have you tried that? did you get the same miserable performance, and the same type of 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 10:05 PM To: Multiple recipients of list ORACLE-LSubject: 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 "ORDER
ID"
,
6 oi.product_id
"PRODUCT I
D",
7 it.quantity
"QUANTITY"
,
8 to_char(round((oi.total/oi.qty_ordered), 2), '999999.00')
9 "UNIT
PRIC
E",
10 'USD'
"CURRENCY"
,
11 'YYYY'
"NAME",
12 1
"REPEAT CU
ST"
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
0
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_id
37
SQL-qadb2->>set autotrace traceonly explain
SQL-qadb2->>/
Execution Plan
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
> -----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 SapovitsReceived on Fri Jun 01 2001 - 11:38:10 CDT
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).
 |
 |