Re: help with multiple BETWEEN statements

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Mon, 13 Jan 2003 15:17:43 -0800
Message-ID: <3E234917.F8219C8@exesolutions.com>

Yvonne G wrote:

> Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote in message news:<v8k42vopjicl7tpr8d28lh5iosp97f1nmd_at_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

> Thanks for the input; I've tried putting liberal parentheses as per
> the following snippet, to no avail:

>


> AND   ADORDER.TYPE = 'C'         AND

>          ((((RUNINS.CLASS) Between ' 105' And ' 820'))

>          OR (((RUNINS.CLASS) Between '4010' And '4320')))

>

> Anyone have additional suggestions?

From your posting(s) it is impossible to tell what it is that is not working as expected. Could you post the DDL to create a sample table, some insert statements that simulate your data, and your SQL. Without a test table I can't tell what might be going on: Or not going on.

Daniel Morgan Received on Mon Jan 13 2003 - 17:17:43 CST