Which of the following methods is faster for retrieving records within a date range?

Option A:
Paid_Date >= '01-JAN-2003' AND Paid_Date <= '01-MAR-2003'

Option B:
Paid_Date BETWEEN '01-JAN-2003' AND '01-MAR-2003'

Thanks!

Just_Buy wrote:

Do your homework on your computer ... not in a usenet group.

It would take you less time, and be substantially less aggravating to run EXPLAIN PLAN
on these two statements than it has been to read this answer and get upset at my
response which doubtless you will choose to do.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

My apologies for not being an Oracle expert like yourself. Since I am
not aware of the "EXPLAIN PLAN" syntax I will research this syntax and
conduct some tests. Once completed I will post my findings back to
this Usenet group so others can learn. Please correct me if I'm
wrong, but learning from others is a goal of these Usenet groups.


Daniel Morgan <damorgan (AT) exxesolutions (DOT) com> wrote

try it. One word of advice don't do it that way. You are counting on a
particular nls date format. Instead wrap the strings in to_date eg
to_date('01-JAN-2003','dd-mmm-yyyy')
Jim

--
Replace part of the email address: kennedy-down_with_spammers (AT) attbi (DOT) com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Just_Buy" <ohbej (AT) hotmail (DOT) com> wrote

I think that it will matter more if you have indexes and such on the table
in question than on the method that you use to do the compare. I ran the
following test (on 8.1.7 via remote sqlnet hookup) and for the 10,000
iterations the difference was less than 20 - with between being slower.

=========
DECLARE
answer INTEGER;

l_date DATE;

l_first DATE;
l_end DATE;

start_time binary_integer;
end_time binary_integer;

BEGIN
l_date := SYSDATE;

l_first := To_Date( '01-JAN-2003', 'dd-mon-yyyy' );
l_end := To_Date( '01-MAR-2003', 'dd-mon-yyyy' );

start_time := Dbms_Utility.get_time;

FOR l_looper in 1..10000
loop
SELECT 1
INTO answer
FROM dual
WHERE 1 = 1
OR ( l_date >= l_first
AND l_Date <= l_end
);
END LOOP;

end_time := Dbms_Utility.get_time;

Dbms_Output.put( '">= <=" elapsed=' );
Dbms_Output.put_line( end_time - start_time );

start_time := Dbms_Utility.get_time;

FOR l_looper in 1..10000
loop
SELECT 1
INTO answer
FROM dual
WHERE 1 = 1
OR ( l_date between l_first AND l_end
);
END LOOP;

end_time := Dbms_Utility.get_time;

Dbms_Output.put( '"between" elapsed=' );
Dbms_Output.put_line( end_time - start_time );

END;
=========
"Just_Buy" <ohbej (AT) hotmail (DOT) com> wrote

You should be converting the constants in both cases to dates by using
to_date. It would be very efficient and the code would be less prone to
breaking if he did that.
Jim

--
Replace part of the email address: kennedy-down_with_spammers (AT) attbi (DOT) com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"M" <noreply (AT) noreply (DOT) com> wrote

Just_Buy wrote:

You missed the point. Everyone can run EXPLAIN PLAN. And what they will learn about is their specific
system with their specific data. It is not necessarily transferrable to anyone else on a different system
with
different data.

So by all means share ... but don't expect that the next time you do an application the result from this one
will apply to the next.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

"Daniel Morgan" <damorgan (AT) exxesolutions (DOT) com> wrote

Daniel,

that's not strictly true. To run EXPLAIN PLAN a user must have the necessary
priveleges to either access an existing "PLAN TABLE" or to create one of
thier own. These priveleges aren't automatically granted to every user !!

Paul Dixon

Paul Dixon wrote:

Necessary privileges? What privileges? There is no privilege associated with
EXPLAIN PLAN.

Now if you don't have CREATE TABLE that could be a problem. But if you don't
have CREATE TABLE you aren't a developer.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Daniel Morgan <damorgan (AT) exxesolutions (DOT) com> wrote in
news:3F006029.ECE34F77 (AT) exxesolutions (DOT) com:

A shame that you have to give a developer CREATE TABLE on a production
machine to replicate the problem that 'only shows up on a production
instance.'
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com