NetNews Usenet Archive 1993 #1/NN_1993_1.iso/spool/comp/database/ingres/2238

Text File | 1993-01-12 | 4.2 KB | 111 lines

Newsgroups: comp.databases.ingres
Path: sparky!uunet!zaphod.mps.ohio-state.edu!pacific.mps.ohio-state.edu!linac!uchinews!gsbacd.uchicago.edu!cs_mj
From: [e-mail] (Mark Jaeger)
Subject: Re: SQL where claus limitations
Message-ID: <[e-mail]>
Lines: 99
Sender: [e-mail] (News System)
Organization:
References: <[e-mail]> <[e-mail]> <[e-mail]> <[e-mail]>
Date: Tue, 12 Jan 1993 19:43:31 GMT

In article <[e-mail]>, [e-mail]
(Jim Buntrock) writes:
> I have been writing a retrieval application that generates a dynamic
> where clause for a select statement.  Depending on complexity of the query
> this where-clause can get quite lengthy.  I know this query
> may be written differently, however, I am working with a dynamic
> query generator, which may produce thousands of different where-clauses.  The
> following where-clauses are just a few examples:
>
>   1> select *
>   2> from sirsworld s
>   3> where
>   4>        (s.proc = '14.24' and s.diag = '100')
>   5>    or  (s.proc = '14.25' and s.diag = '200')
>   6>    or  (s.proc = '14.26' and s.diag = '300')
>   7>    or  (s.proc = '14.30' and s.diag = '400')
>   8>    or  (s.proc = '14.31' and s.diag = '500')
>   9>    or  (s.proc = '14.32' and s.diag = '600')
>  10>    or  (s.proc = '14.33' and s.diag = '700')
>  11>  order by 1, 2
> E_OP0302 too many boolean
>     factors defined in query - query too complex

> If I add two more conjunctions I get the following error:
>
>   1> select *
>   2> from sirsworld s
>   3> where
>   4>        (s.proc = '14.24' and s.diag = '100')
>   5>    or  (s.proc = '14.25' and s.diag = '200')
>   6>    or (s.proc = '14.26' and s.diag = '300' )
>   7>    or (s.proc = '14.27' and s.diag = '301' )
>   8>    or (s.proc = '14.28' and s.diag = '302' )
>   9>    or  (s.proc = '14.30' and s.diag = '400')
>  10>    or  (s.proc = '14.31' and s.diag = '500')
>  11>    or  (s.proc = '14.32' and s.diag = '600')
>  12>    or  (s.proc = '14.33' and s.diag = '700')
>  13>  order by 1, 2
> E_OP0002 optimizer ran out of
>     memory before generating execution plan

> I have tried increasing the memory of the QEP and still run into the
> OUT of memory error.  My concern is with the error of - E_OP0302 too
> many boolean factors defined in query - query too complex -
> Does anyone know the limit of the number of boolean factors?  Can this
> be changed?  Is this a finite limitation of Ingres?  Is the limit based
> on conjunctions or disjunctions?  Any suggestions?
>
> ///////////////////////////////
> James Buntrock
> Analyst/Programmer
> Medical Information Resources
> Mayo Clinic
> 200 First Street SW
> Rochester, MN 55905
> email - [e-mail]

We had exactly the same problem, due to exactly the same thing:
mechanical SQL code generation.  Here's a sample to reproduce it:

    create table t ( x i4 not null ) ;

    select distinct x from t
    where  x between 1 and 2
        or x between 3 and 4
        or x between 5 and 6
        or x between 7 and 8
        or x between 9 and 10
        or x between 11 and 12
        or x between 13 and 14
        or x between 15 and 16
        or x between 17 and 18
    ;

We also got the two error messages:

o with 6 conditions or less, the query works

o with 7-8 conditions, we get E_OP0302,
    too many boolean factors defined in query--query too complex

o with 9 or more conditions, we get E_OP0002,
    optimizer ran out of memory before generating execution plan

Increasing OPF memory didn't help, so this seems to be an architectural
limitation.  Ingres Tech Support has logged bug #43697 for me.  The
suggested workaround is to break up the query into a series of unions.
In our case, we were able to use the "in" predicate as well, and this
seemed to work better.

According to TS, the two different error messages are due to traveling
through different code paths in the optimizer.

--Mark Jaeger                internet: [e-mail]
Graduate School of Business        yellnet:  [phone]
University of Chicago            faxnet:   [phone]
Disclaimer: My opinions are my own and not those of my employer.
Je suis un virus.  Prends ton pied et copie moi a ta .signature.