Re: Which is faster? <> or Between

From: Scott Mattes <Scott_at_TheMattesFamily.ws>
Date: Thu, 26 Jun 2003 13:49:48 GMT
Message-ID: <0SCKa.18398$Jw6.7477476@news1.news.adelphia.net>

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.com> wrote in message news:65275ae5.0306251533.24adf1b3_at_posting.google.com...
> 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.com> wrote in message
news:<3EF9C9FA.2621AE77_at_exxesolutions.com>...
> > Just_Buy wrote:
> >
> > > 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!
> >
> > 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.
Received on Thu Jun 26 2003 - 08:49:48 CDT