Re: help with multiple BETWEEN statements

From: Yvonne G <hlngus_at_hotmail.com>
Date: 15 Jan 2003 15:54:07 -0800
Message-ID: <d999b872.0301151554.4295efb2@posting.google.com>

Here's the entire code, which just runs for over 1 hr without returning any data; it runs in just about 1 min with just 1 BETWEEN statement, so I suspect the culprit to be the addition of another BETWEEN statement:

SELECT DISTINCT

        ADORDER.ORDERNO, 
	ADORDER.ENDDATE, 
	ADORDER.CHANGETS, 
	ADORDER.GROSSPRICE, 
	ADORDER.BASEPRICE

FROM
    PPI.ADORDER ADORDER,
    PPI.RUNINS RUNINS WHERE

     ADORDER.ORDERNO = RUNINS.ORDERNO  
	 AND   ADORDER.TYPE = 'C'   
	 AND      ADORDER.ENDDATE > ('06-jan-2003')     
	 AND   ADORDER.KILLDATE >=  ('01-jan-1960')
	 AND   ADORDER.ORDERNO < '   3482364' 
	 AND      ADORDER.KILLDATE < ('02-jan-1960')   
	 AND RUNINS.RATECODE  NOT IN ('IP','XP')
	 AND
	 ((((RUNINS.CLASS) Between ' 105' And ' 820'))
	 OR (((RUNINS.CLASS) Between '4010' And '4320')))
	 	 
	 MINUS
	 
	 SELECT DISTINCT
           ADORDER.ORDERNO, 
	   ADORDER.ENDDATE, 
	   ADORDER.CHANGETS, 
	   ADORDER.GROSSPRICE, 
	   ADORDER.BASEPRICE    

FROM
    PPI.ADORDER ADORDER,
    PPI.HOLDORDER HOLDORDER WHERE
    ADORDER.TYPE = 'C'

	AND      ADORDER.ENDDATE > ('06-jan-2003')     
	AND   ADORDER.KILLDATE >=  ('01-jan-1960') 
	AND      ADORDER.KILLDATE < ('02-jan-1960')     
	AND   ADORDER.ORDERNO = HOLDORDER.HOLDORDERNO
	AND   HOLDORDER.HOLDRELDATE = ('01-jan-1960')
	AND   ADORDER.ORDERNO < '   3482364'

Anyone care to impart some wisdom?

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E234917.F8219C8_at_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 Wed Jan 15 2003 - 17:54:07 CST