Pb with an SQL request

Pb with an SQL request

Post by Marianne Boud » Fri, 26 Jun 1992 17:43:54



Hello

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




(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 = 'manage


.hisal)

The SQL request seems to be correct.
Could one of you tell me if it could be a problem of installation ?
(parameters to change...).
Reply by mail.
Thanks in advance

--
 Marianne Boudot
 System Engineer                                  
 Inria Centre de Sophia Antipolis | Tel: (33) 93 65 77 27
 2004, Route des Lucioles         | Fax: (33) 93 65 76 02

 06561 Valbonne Cedex (France)    | X400 :  C=fr; A=atlas; P=inria; OU=sophia;
                                  |         S=Boudot; G=Marianne

 
 
 

Pb with an SQL request

Post by Mark Jaeg » Sat, 27 Jun 1992 07:56:51



writes:

> 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:

        where 1=1
        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.

 
 
 

Pb with an SQL request

Post by Gavin Tri » Sat, 27 Jun 1992 10:26:15



>Hello
>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 :

< stuff deleted >

Quote:>The SQL request seems to be correct.
>Could one of you tell me if it could be a problem of installation ?
>(parameters to change...).
>Reply by mail.
>Thanks in advance

Look in the Installation and Operations Guide for the Server Parameters.  By
default each user gets 200000 bytes of optimizer space.  You will probably
need to set op.active and op.memory based on the number of sessions.  Here is
an example of the server parameters from one of my installations-

-names
-connected_sessions 110
-active_sessions 110
-cursors_per_session 32
-database_count 36
-stack_size 65536
-fast_commit
-shared_cache
-write_behind 4
-session_accounting
-opf.active 10
-opf.memory 20000000
-quantum 1000
-qsf.pool_size 5000000

>--
> Marianne Boudot
> System Engineer                              
> Inria Centre de Sophia Antipolis | Tel: (33) 93 65 77 27
> 2004, Route des Lucioles     | Fax: (33) 93 65 76 02

> 06561 Valbonne Cedex (France)    | X400 :  C=fr; A=atlas; P=inria; OU=sophia;
>                              |         S=Boudot; G=Marianne


                Telephone: +61 3 344 6003       Fax +61 3 347 4803
Data Base Administrator, Information Technology Services (ASIS),
The University of Melbourne, Parkville, Australia.  3052.