Home » RDBMS Server » Performance Tuning » Is Between faster?
Is Between faster? [message #65044] Tue, 06 April 2004 06:53 Go to next message
Vasantha
Messages: 2
Registered: April 2004
Junior Member
Hi

Quick Question.

If my query has the following where clause:

Where ID between 10 and 100;

Is this faster than using this clause:

Where ID >= 10 and

ID <= 100;
Re: Is Between faster? [message #65046 is a reply to message #65044] Tue, 06 April 2004 07:30 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
I think they are about the same, I would use BETWEEN. But if it is vital for you or if you are just curious you should run some tests to see which is faster.
There is number of factors that can give different answers so it is always better to prove everything empirically for given environment as well as in generally.
Re: Is Between faster? [message #65048 is a reply to message #65044] Tue, 06 April 2004 07:43 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
No, its not faster. It should be about the same.

SQL> drop table t;

Table dropped.

SQL> create table t as select * from scott.emp;

Table created.

SQL> create index t_idx on t(empno);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> set autotrace on

SQL> select empno from t where empno >=1000 and empno <= 10000;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=14 Bytes=42)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=14
           Bytes=42)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          <B>2  consistent gets</B>
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> select empno from t where empno between 1000 and 10000;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=14 Bytes=42)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=14
           Bytes=42)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          <B>2  consistent gets</B>
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

Re: Is Between faster? [message #65061 is a reply to message #65044] Sun, 11 April 2004 14:51 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
BETWEEN is converted by the optimizer into ">=" and "<=" conditions.

Where possible I would always use BETWEEN as it avoids repeating the test expression, and states the condition in one line instead of two.
Previous Topic: DBA_SEGMENTS Alternate code?
Next Topic: How indexes behave on a varchar field
Goto Forum:
  


Current Time: Fri Sep 25 03:14:20 CDT 2020