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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
> [EMAIL PROTECTED]
> 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: [EMAIL PROTECTED]
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: [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).
