Re: About BETWEEN and the Optimizer

On Wed, 12 Dec 2001 23:07:41 +0100, Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

>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

Thank you for responding. I want to make sure I understand all this.

Brian Received on Thu Dec 13 2001 - 12:41:46 CST