| 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()
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-LSubject: 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.
Work Email: sapovitss_at_globalsports.com Home Email: steves_at_delanet.com Work Phone: 610-491-7087
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve SapovitsReceived on Thu May 31 2001 - 10:19:36 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).
 |
 |