| 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()
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.
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: Ron Rogers [SMTP:RROGERS_at_galottery.org]
> 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
>
> >>> lisa.koivu_at_efairfield.com 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: RROGERS_at_galottery.org
>
> 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 Fri Jun 01 2001 - 16:55:19 CDT
 |
 |