| 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()
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:
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.
Work Email: sapovitss_at_globalsports.com Home Email: steves_at_delanet.com Work Phone: 610-491-7087
> -----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
> 3 to_char(it.transaction_date,'MMDDYYYY HH24:MI:SS')
> 4 substr(co.orso_code,4,length(co.orso_code) - 3)
> 5 it.order_id
> 6 oi.product_id
> 7 it.quantity
> 8 to_char(round((oi.total/oi.qty_ordered), 2),
> 9
> 10 'USD'
> 11 'YYYY'
> 12 1
> 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) <
> 31 AND co.order_id = it.order_id > 32 AND co.orso_type = 'BF' > 33 AND substr(co.orso_code, 1, 2) =
> 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
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2532 Card=1 Bytes=14 > 1)
> 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)
> ost=1 Card=1)
> 52 Card=1 Bytes=25)
> 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)
> (NON-UNIQUE) (Cost=19 Card=453)
> (Cost=3 Card=2277488 Bytes=47827248)
> -UNIQUE) (Cost=2 Card=2277488)
> t=2 Card=9060 Bytes=135900)
> 1 Card=9060)
> INET: SapovitsS_at_globalsportsinc.com
 |
 |