From: Date: February 20 2003 6:43am
Subject: Re: Space for results table exhausted
List-Archive: http://lists.mysql.com/maxdb/12558
Message-Id: <200302200643.24717.willadt@t-online.de>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

Hello,
On Mittwoch, 19. Februar 2003 19:20, JON wrote:
> 
>  I have the following query created with the visual query tool:
> SELECT
> "OCPC"."TBLARCHOWNER"."NAME","OCPC"."TBLPROJAO"."SHOWONRESULTS","OCPC"."TBL
>A
> RCHOWNER"."PHONE","OCPC"."TBLARCHOWNER"."ZIP","OCPC"."TBLARCHOWNER"."STATE"
>, "OCPC"."TBLARCHOWNER"."CITY","OCPC"."TBLARCHOWNER"."ADDR1" FROM
> "OCPC"."TBLAOTYPE","OCPC"."TBLARCHOWNER","OCPC"."TBLPROJAO" WHERE
> "OCPC"."TBLARCHOWNER"."ID"= "OCPC"."TBLPROJAO"."ARCHOWNERID" (+) AND
> "OCPC"."TBLAOTYPE"."AOTYPEID"= "OCPC"."TBLPROJAO"."AOTYPEID" (+) AND
> ((tblProjAO.ShowOnResults)=TRUE) and tblProjAO.ProjID = 8329 order by
> ProjAO When I execute the query I get the Space for results table exhausted
> error. I'm using  kernal 7.3.0,  SQL Studio 7.4.3.6, ODBC driver
> 7.04.03.00.
>
exhausting space for results mostly comes from building cartesian products. 
But in this case, it doesn't look like that.
Generally, it might be goos to put the most restricting conditions first (in 
this case the last both) or to build a view resulting on these conditions and 
have the query use this view instead of the underlying base table(s).

In this particular case I wonder what reason there is to include TBLAOTYPE in 
the query. It seems not to restirct the result set nor to appear in the 
output. I also wonder where the ProjAO in the order by clause comes from.

Perhaps you might try rewriting your query like this and try again.

SELECT
TBLARCHOWNER.NAME,TBLPROJAO.SHOWONRESULTS,TBLARCHOWNER.PHONE,
TBLARCHOWNER.ZIP,TBLARCHOWNER.STATE,
TBLARCHOWNER.CITY,TBLARCHOWNER.ADDR1 FROM
TBLARCHOWNER,
TBLPROJAO 
WHERE tblProjAO.ShowOnResults=TRUE 
and tblProjAO.ProjID = 8329 
and TBLARCHOWNER.ID= TBLPROJAO.ARCHOWNERID (+) 
order by ProjAO

Peter Willadt