OraFAQ Forum: Performance Tuning » Is Between faster?

Home » RDBMS Server » Performance Tuning » Is Between faster?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is Between faster? [message #65044]

Tue, 06 April 2004 06:53

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;

Report message to a moderator

Re: Is Between faster? [message #65046 is a reply to message #65044]

Tue, 06 April 2004 07:30

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.

Report message to a moderator

Re: Is Between faster? [message #65048 is a reply to message #65044]

Tue, 06 April 2004 07:43

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

Report message to a moderator

Re: Is Between faster? [message #65061 is a reply to message #65044]

Sun, 11 April 2004 14:51

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.

Report message to a moderator

Previous Topic:	DBA_SEGMENTS Alternate code?
Next Topic:	How indexes behave on a varchar field

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 22:54:00 CDT 2024