Re: help with multiple BETWEEN statements

From: Sybrand Bakker <gooiditweg_at_nospam.demon.nl>
Date: Mon, 13 Jan 2003 06:42:55 +0100
Message-ID: <v8k42vopjicl7tpr8d28lh5iosp97f1nmd@4ax.com>

On 12 Jan 2003 21:04:14 -0800, hlngus_at_hotmail.com (Yvonne G) wrote:

>thanks to those for correcting me:
>
>however, when there is only 1 between statement the query comes back
>quickly.
>
>when there are multiple OR clauses, the query just hangs and eats up
>system resources. What is happening?
>
>SELECT A from table1
>> where rate = 'abc'
>> and code between '0100' and '0199'
>> OR code between '4000' and '4499'
>
>thanks again.

Mathematically a AND has a higher priority than OR Try putting parentheses around the various conditions

and (code between '0100' and '0199') or (code between '4000' and '4499')
I have seen awful results by not doing this. You might also want to apply the /*+ USE_CONCAT */ hint in your query, this will make sure you will the ORs dealt with as multiple query blocks.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Jan 12 2003 - 23:42:55 CST