Re: IN or BETWEEN

miotromailcarlos_at_netscape.net (Carlos) wrote in message news:<1de5ebe7.0204230113.42cd2d4d_at_posting.google.com>...
> Hello everyone.
> I am designing a DB with Oracle Designer. When I add 'allowable
> values' for a column I have two choices:
> 1.- Adding values separately (i.e: adding '0' as a 'allowable value'
> and then adding '1' as another 'allowable value'). This will generate
> a check constraint of type IN ( x_column IN (0,1)).
> 2.- Adding values by intervals (i.e: adding '0' as a 'allowable value'
> with '1' as 'high value'). This will generate a check constraint of
> type BETWEEN ( x_column BETWEEN 0 AND 1 ).
> I always assumed that expresions of type 'BETWEEN' are quicker than
> expressions of type 'IN' (at least talking in Oracle terms).
> Am I right in this assumption?
> And yet another question: Do expresions of type 'BETWEEN' work like
> expresions with <= and >= or not?
> Thanks in advance and regards from Spain.

x between a and b is equivalent to
(x >= a and x<=b)
Note the (.
If you have
y > c and x>=a and x<=b this might be evaluated as (y > c and x>= a) and x<=b
with detrimental effect on the performance

Whether IN is slower depends on the version of the DB (which you of course don't mention). In 8i and higher the cost based optimizer will use a so-called 'in-list operator', so it shouldn't make much difference. The RBO, being obsolete since 1994, is not aware of this feature.
As usual: more information is needed to provide suitable advice, so it depends.

Hth
Sybrand Bakker
Senior Oracle DBA Received on Tue Apr 23 2002 - 15:31:57 CEST