| 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()
Thanks. I have Harrison's book but you're a few steps ahead of me reading and understanding it. What disturbs me is this: The table in question is relatively small -- under 200,000 rows so far. It seems that Oracle has "optimized" things into some black hole that's far worse than just doing a row-by-row scan. The fact that something optimized can perform far worse than just using flat files causes me to lose sleep.
Any other Oracle insights would be greatly appreciated. I want to understand how The Beast thinks. 8-)
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: Friday, June 01, 2001 11:45 AM
> To: 'Steve Sapovits'; 'oracle-l_at_fatcity.com'
> Subject: RE: problem with trunc()
>
> Wow. the only thing that stands out is the range scan. In Harrison's SQL
> tuning book, there is a discussion about how a range lookup is actually
> pretty stupid. On page 132:
>
> "to understand why Oracle's retrieval plan seems to poor, we have to
> recognize the hidden assumptions we make when formulating our "mental"
> exection plan. for instance, Oracle does not know that lowval is always
> less tha highval, whereas we know this intuitively from the names of the
> columns. Furthermore, we assme that there are no overlaps between rows
> (that any given nmber only matches a single val) Oracle cannot assume
> this.
>
> without knowing what we know about the data, the optimizer must perform
> the followin steps:
>
> 1. Search the index to find a row where the lowval is less than the number
> specified. this will be the first (lowest) matching entry in the index.
>
> 2. Checks to see if hte highval is greater than the number specified.
> 3. If it is not, check the next index entr.
> 4. continue performing a range scan of this nature until it finds an
> entry where loval is higher than the number provided. the entry just
> prior to this entry will be the correct entry.
>
> so in essence, the opimizer must perform a range scan from the lowest
> range in the index until the row after the range for which we're looking.
> On average, then, half of the index will be scanned. "
>
> He then goes on to suggest rownum=1 as a fix (not always possible) or
> pl/sql block to shortcut this behavior.
>
> I hope this helps you. Guy Harrison's book (ISBN 0136142311) is an
> excellent starting point for learning about the intricacies of tuning sql.
> there is a newer version than this one I am referencing though.
>
> have a great weekend.
> 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: Friday, June 01, 2001 10:30 AM
> To: Lisa Koivu; 'ORACLE-L_at_fatcity.com';
> 'sapovitss_at_globalsportsinc.com'
> Subject: RE: problem with trunc()
>
>
> 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: 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: Friday, June 01, 2001 10:36 AM
> > To: 'ORACLE-L_at_fatcity.com'; 'sapovitss_at_globalsportsinc.com'
> > 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:SapovitsS_at_globalsportsinc.com]
> > 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: 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).
> >
>
-- 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 Fri Jun 01 2001 - 13:03:40 CDT
 |
 |