Path: news.easynews.com!easynews!news.alt.net!usenet
From: SPAMBLOCK.Maxwell_Smart@ThePentagon.com.SPAMBLOCK (Brian Tkatch)
Newsgroups: comp.databases.oracle.misc
Subject: About BETWEEN and the Optimizer
Date: Wed, 12 Dec 2001 18:35:34 GMT
Organization: Altopia Corp. - Usenet Access - http://www.altopia.com
Lines: 29
Message-ID: <3c17a231.2001850156@news.alt.net>
X-Newsreader: Forte Free Agent 1.11/32.235
Xref: easynews comp.databases.oracle.misc:73686
X-Received-Date: Wed, 12 Dec 2001 11:34:40 MST (news.easynews.com)

Oracle 8.1.7

I am reading the documentation on optimization. Chapter 4, the
Optimizer. It gives an example (6):

===
The following query uses the BETWEEN operator to select all employees
with employee ID numbers between 7500 and 7800: 

SELECT * 
  FROM emp 
  WHERE empno BETWEEN 7500 AND 7800; 


To determine the selectivity of this query, the optimizer decomposes
the WHERE clause condition into these two conditions: 

empno >= 7500 
empno <= 7800 
===

This question is more out of curiosity than anything else. 

Does using BETWEEN have any advantages over two separate caluses that
check < and >? BETWEEN is possibly better for readability, but I am
not asking about that. Does BETWEEN somehow let the optimizer tie the
two WHERE clauses together where it otherwise would not?

Brian