RE: Tuning help required

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 24 Sep 2003 12:04:39 -0800
Message-ID: <F001.005D0F34.20030924120439@fatcity.com>

This probably doesn't apply to the original question, but I would like to point out that MEPAI.MPAI_AS_OF_DATE between to_date('03/01/2003','MM/DD/YYYY') and to_date('03/31/2003','MM/DD/YYYY') is not the same as
MEPAI.MPAI_AS_OF_DATE in (to_date('03/01/2003','MM/DD/YYYY'), ..., to_date('03/31/2003','MM/DD/YYYY')) unless all the dates have 0:0:0 for the time portion, for example because of a before insert or update trigger that sets :new.mpai_as_of_date := trunc (:new.mpai_as_of_date)

Try this and see the difference.
create table orders (order_id number (4), order_date date) ; begin

   for i in 1..24
   loop

      for j in 1..30
      loop
         insert into orders (order_id, order_date)
          values (j + 30 * (i - 1),
                  to_date ('200309' || to_char (j, 'FM09') || to_char (i - 1, 'FM09'),
                           'YYYYMMDDHH24')
                 ) ;
      end loop ;

   end loop ;
   commit ;
end ;
/
select count (*) from orders
 where order_date between to_date ('20030901', 'YYYYMMDD')

                  and to_date ('20030930', 'YYYYMMDD') ;

select count (*) from orders
 where order_date in

  (to_date ('20030901', 'YYYYMMDD'),
   to_date ('20030902', 'YYYYMMDD'),
   to_date ('20030903', 'YYYYMMDD'),
   to_date ('20030904', 'YYYYMMDD'),
   to_date ('20030905', 'YYYYMMDD'),
   to_date ('20030906', 'YYYYMMDD'),
   to_date ('20030907', 'YYYYMMDD'),
   to_date ('20030908', 'YYYYMMDD'),
   to_date ('20030909', 'YYYYMMDD'),
   to_date ('20030910', 'YYYYMMDD'),
   to_date ('20030911', 'YYYYMMDD'),
   to_date ('20030912', 'YYYYMMDD'),
   to_date ('20030913', 'YYYYMMDD'),
   to_date ('20030914', 'YYYYMMDD'),
   to_date ('20030915', 'YYYYMMDD'),
   to_date ('20030916', 'YYYYMMDD'),
   to_date ('20030917', 'YYYYMMDD'),
   to_date ('20030918', 'YYYYMMDD'),
   to_date ('20030919', 'YYYYMMDD'),
   to_date ('20030920', 'YYYYMMDD'),
   to_date ('20030921', 'YYYYMMDD'),
   to_date ('20030922', 'YYYYMMDD'),
   to_date ('20030923', 'YYYYMMDD'),
   to_date ('20030924', 'YYYYMMDD'),
   to_date ('20030925', 'YYYYMMDD'),
   to_date ('20030926', 'YYYYMMDD'),
   to_date ('20030927', 'YYYYMMDD'),
   to_date ('20030928', 'YYYYMMDD'),
   to_date ('20030929', 'YYYYMMDD'),
   to_date ('20030930', 'YYYYMMDD')

  ) ;

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> jo_holvoet_at_amis.com
> Sent: mercredi, 24. septembre 2003 02:20
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Tuning help required
>
>
> Well, I'd start by writing the date part as :
>
> MEPAI.MPAI_AS_OF_DATE between
> to_date('03/01/2003','MM/DD/YYYY') and
> to_date('03/31/2003','MM/DD/YYYY')
>
>
> It will at the very least make the query easier to read and
> understand
> (also for the optimizer : it will know it's filtering on a
> range instead
> of distinct values).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Wed Sep 24 2003 - 15:04:39 CDT