Range variable limits

schlegel@informatik.uni-rostock.de (Juergen Schlegelmilch) wrote:

>Hello,
>I'm using INGRES 6.4 and have a problem with its SQL interpreter.
>My application has about 10 tables and derives several views from
>these. One view combines six other tables or views into one --- if
>INGRES would accept it. Up to five is ok, adding a sixth one causes
>the error message
>  E_US0C1C Query requires too many range variables

>The manual tells me that INGRES Quel supports only 30 range variables
>but since I use SQL, I do not have any influence on the use of range
>variables. 
>It would be fine if this number could be raised by some server parameter
>or the like, but I could not find any hint in the manuals.

>FWIW, I include the definition of the offending view at the end of the
>posting, together with the definitions of the tables. Note that this is
>not a toy problem, but a real application to help organizing the projects
>of our research group. I would be very disappointed if INGRES is not able
>to handle these views.

>My questions are:
>- How can I tweak INGRES to allow more range variables ?
>- Which constructs of SQL are costly WRT range variables ?
>- Shall I use triggers instead of views ? I wanted to stay away from such
>  low-level optimization until the end of the core implementation, to avoid
>  problems in this early phase.

>Thanks in advance,
>  Juergen Schlegelmilch

>-- 
>+-----------------------------------------------------------------------------+
> Dipl.-Inf. Juergen Schlegelmilch                 University of Rostock
> email: schlegel@Informatik.Uni-Rostock.de        Computer Science Department
> http://www.informatik.uni-rostock.de/~schlegel   Database Research Group 
> Tel: ++49 381 498 3402                           18051 Rostock 
> Fax: ++49 381 498 3404                           Germany
>+-----------------------------------------------------------------------------+

At the risk of spoiling all of your fun with the joins, I would like
to suggest that sometimes there is a simpler path.

When dealing with the kind of situation you layed out I find it is
much easier to do the query by creating another table to hold the
results and then loading it up with passes through all of your
selected tables:

create table results (column1, column2, colum3, etc...)

insert into table results select (column1 ......)
from first_target

insert into table results select (column1 ......)
from next_target

insert into table results select (column1 ......)
from next_target


select * from results


Even though you lose a bit of performance by using the insert, it is
very effective and you don't have to worry about how many tables you
are going through to produce the final result.


Sometimes the sum of the parts is much faster than using union.

Michael Greene
Database Administrator
Bowater Mersey Paper Co. Ltd
Liverpool, N.S.





On May 8, 15:39, Juergen Schlegelmilch wrote:
> Subject: [Q] SQL: too many range variables ?

> My application has about 10 tables and derives several views from
> these. One view combines six other tables or views into one --- if
> INGRES would accept it. Up to five is ok, adding a sixth one causes
> the error message
>   E_US0C1C Query requires too many range variables
> ...
> My questions are:
> - How can I tweak INGRES to allow more range variables ?

You can't.  The limit of 30 is hard coded in as far as I know.
My guess is that it has to do with the use of bitmaps somewhere in
the code, either the parser or optimizer or both.


> - Which constructs of SQL are costly WRT range variables ?

Every table reference (ie correlation name) in a FROM clause counts as a
"range variable".  The unions are nailing you.

-- 
Karl Schendel            Phone: (412) 963-8844
Telesis Computer Corp      Fax: (412) 963-1373
wiz@telesis.com
Ingres Q & A
To William's Home Page
Email William