> We have Ingres Version 6.3/02 running on Sun OS4.1.1 on a Sparcstation 1.
> One of our developpers has got a SQL error with a request created by a
> generator :
> ** sql-error : code is 29436 error is msg error is : E_OP0002 optimizer ran
> out of memory before generating execution plan
> where (
> (emp.sal >= 700. and emp.sal <= 9999.)
> or (emp.job = 'salesman' and (emp.sal >= 700. and emp.sal <= 9999.))
> or (emp.job in ('manager','president') and (emp.sal >= 700. and emp.sal <= 9999.))
> or (emp.job = 'president' and (emp.sal >= 700. and emp.sal <= 9999.))
> or (emp.job = 'manager' and (emp.sal >= 700. and emp.sal <= 9999.))
> The SQL request seems to be correct.
> Could one of you tell me if it could be a problem of installation ?
> (parameters to change...).
We had a similar problem with generated queries. The large number of
"or" conditions along with the "and" conditions is probably the source
of the problem. I don't know much about database theory, but apparently
the INGRES query optimizer tries to re-express the query in terms of one
kind of expression. It sort of multiplies out the "and" and "or"
conditions, and there are so many combinations that the query explodes.
The server runs out of memory.
We ran into this problem when trying to find addresses in a particular
zip code area. The "where" clause looked like this:
and ( zip between 60601 and 60603
or zip between 60608 and 60610
or zip between 60688 and 60699
The "between" predicates generate an "and" condition, and the
multiplicative effect is tremendous. INGRES couldn't handle more than 6
of the zip code ranges. It choked on 7 or more. Can anyone from Ingres
out there tell us more about how this works?
You can try changing the server parameters: increase -opf.memory and
decrease -opf.active, but this may not help. The query may be just too
complicated for INGRES to handle. You might have to express the query
in a different way. Look in the I & O Guide (Installation and
Operations) for details on the server parameters. The copy I have is
for release 6.4. Since you're using an earlier version, some of these
parameters may not be there.
University of Chicago yellnet: (312) 702-0328
Graduate School of Business faxnet: (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus. Mach' mit und kopiere mich in Deine .signature.