Re: About BETWEEN and the Optimizer

On Wed, 12 Dec 2001 18:35:34 GMT,
SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote:

>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

As to your last question : YES
I have seen some disastrous examples where the between was reformulated and got chopped, resulting in full table scans. This has to do with arithmetic priorities. I'm also almost sure if you have proper () the difference shouldn't be noticeable.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Dec 12 2001 - 16:07:41 CST