| 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()
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 ...
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).Received on Thu May 31 2001 - 10:46:23 CDT
 |
 |