Yes, as mentioned I've rewritten to get rid of the trunc().
But being a masochist I want to know *why* Oracle goes so
far out to lunch with that one small change. It doesn't
make sense to me based on what I know about the optimization
process, SQL, etc. Admittedly, I probably know far too little.
I was hoping someone could give me that glimpse into Oracle
thinking that will ultimately help me write better queries.
----
Steve Sapovits
Global Sports Interactive
Work Email: [EMAIL PROTECTED]
Home Email: [EMAIL PROTECTED]
Work Phone: 610-491-7087
Cell: 610-574-7706
Pager: 877-239-4003
> -----Original Message-----
> From: Lisa Koivu [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, June 01, 2001 10:36 AM
> To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
> Subject: 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:[EMAIL PROTECTED]]
> Sent: Thursday, May 31, 2001 10:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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
> ----------------------------------------------------------
> 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)
>
>
>
> ----
> Steve Sapovits
> Global Sports Interactive
> Work Email: [EMAIL PROTECTED]
> Home Email: [EMAIL PROTECTED]
> Work Phone: 610-491-7087
> Cell: 610-574-7706
> Pager: 877-239-4003
>
> > -----Original Message-----
> > From: Steve Sapovits [SMTP:[EMAIL PROTECTED]]
> > 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: [EMAIL PROTECTED]
> > Home Email: [EMAIL PROTECTED]
> > Work Phone: 610-491-7087
> > Cell: 610-574-7706
> > Pager: 877-239-4003
> >
> > > -----Original Message-----
> > > From: Lisa Koivu [SMTP:[EMAIL PROTECTED]]
> > > Sent: Thursday, May 31, 2001 11:43 AM
> > > To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
> > > 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:[EMAIL PROTECTED]]
> > > 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: [EMAIL PROTECTED]
> > > Home Email: [EMAIL PROTECTED]
> > > 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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
