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).
