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: [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 ('' 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 ...
1SELECT ''
MERCHANT
ID,
2 it.transaction_type
TYPE,
3 to_char(it.transaction_date,'MMDD 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),
'99.00')
9
UNIT
PRIC
E,
10 'USD'
CURRENCY
,
11 ''
NAME,
12 1
REPEAT CU
ST
13FROM 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
RE: problem with trunc()
Title: 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 ('' 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 ''
MERCHANT
ID,
2 it.transaction_type
TYPE,
3 to_char(it.transaction_date,'MMDD 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), '99.00')
9 UNIT
PRIC
E,
10 'USD'
CURRENCY
,
11 ''
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-') AND
29 to_date('May-28-2001', 'Mon-dd-')
30 AND to_number(it.sku) 9 and to_number(it.sku)
800
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
RE: problem with trunc()
Title: 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:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 10:30 AM
To: Lisa Koivu; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
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: [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
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-)
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 11:45 AM
To: 'Steve Sapovits'; '[EMAIL PROTECTED]'
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:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 10:30 AM
To: Lisa Koivu; '[EMAIL PROTECTED]';
'[EMAIL PROTECTED]'
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: [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
RE: problem with trunc()
A select datefield from table where datefield date1 and datefield date2 will
produce the same resulte as specifing the date trunc() or using the time as 00:00:00.
The , will pick time 00:00:00 values to compare against.
ROR mª¿ªm
[EMAIL PROTECTED] 06/01/01 11:25AM
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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
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).
RE: problem with trunc()
Lisa Koivu set me straight. I just needed to reanalyze.
The table was relatively new but I added an index or two
that threw the stats that were there way off.
The result: 7 hours down to 30 seconds. Kind of hard to
believe but true.
I learned something. Thanks to Lisa and everyone else who
offered ideas.
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: Ron Rogers [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 2:51 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: problem with trunc()
A select datefield from table where datefield date1 and datefield
date2 will produce the same resulte as specifing the date trunc() or using
the time as 00:00:00. The , will pick time 00:00:00 values to compare
against.
ROR mª¿ªm
[EMAIL PROTECTED] 06/01/01 11:25AM
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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
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).
RE: problem with trunc()
Title: 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-')
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-') AND
to_date('May-28-2001', 'Mon-dd-')
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).
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-')
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-') AND
to_date('May-28-2001', 'Mon-dd-')
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
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-')
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-') AND
to_date('May-28-2001', 'Mon-dd-')
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).
RE: problem with trunc()
I have always been told that using functions on fields would stop the
efficient use of indexes so . what if you said
WHERE it.transaction_date BETWEEN
to_date('May-19-2001.00.00.00', 'Mon-dd-.hh24.mi.ss') AND
to_date('May-28-2001.23.59.59', 'Mon-dd-')
This would at least eliminate the Trunc and give you another possibility.
Kevin
-Original Message-
Sent: Thursday, May 31, 2001 11:01 AM
To: Multiple recipients of list ORACLE-L
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-')
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-') AND
to_date('May-28-2001', 'Mon-dd-')
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: Kevin Lange
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).
RE: problem with trunc()
You seem to have lost your sig. Are you no longer a 'Wanton Kickboxing Goddess'? ;-) Todd Carlson Oracle 8i Certified DBA Bunge North America -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
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 ('' 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 ...
1SELECT ''
MERCHANT
ID,
2 it.transaction_type
TYPE,
3 to_char(it.transaction_date,'MMDD 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), '99.00')
9 UNIT
PRIC
E,
10 'USD'
CURRENCY
,
11 ''
NAME,
12 1
REPEAT CU
ST
13FROM 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
26WHERE orso_store.store_code = 'FOG'
27 AND trunc(it.transaction_date) BETWEEN
28to_date('May-19-2001', 'Mon-dd-') AND
29to_date('May-28-2001', 'Mon-dd-')
30 AND to_number(it.sku) 9 and to_number(it.sku)
800
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)
10 SORT (ORDER BY) (Cost=2532 Card=1 Bytes=141)
21 NESTED LOOPS (Cost=2527 Card=1 Bytes=141)
32 NESTED LOOPS (Cost=2525 Card=1 Bytes=126)
43 NESTED LOOPS (Cost=153 Card=1 Bytes=43)
54 TABLE ACCESS (BY INDEX ROWID) OF 'ORSO_STORE' (Cos
t=1 Card=1 Bytes=18)
65 INDEX (RANGE SCAN) OF 'SYS_C0015465' (UNIQUE) (C
ost=1 Card=1)
74 TABLE ACCESS (FULL) OF 'ITEM_TRANSACTIONS' (Cost=1
52 Card=1 Bytes=25)
83 VIEW
98 SORT (GROUP BY) (Cost=2372 Card=1600 Bytes=59200)
109 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)
152 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
