RE: Space for results table exhausted

[Noah J SILVA]

Actually,

I had the same problem, even where the results should not have been huge.  
I couldn't see how they would be, and nobody else offered an explanation 
why they would be, but I was just told "they must be".  Since there is a 
work-around, I am not going to spend that much time worrying about it.

It seems though, SAPDB doesn't like queries like this:

select * 
from a, b, c, d, e
where a.key=b.key
and a.key=c.key(+)
and a.key=d.key(+)
and a.key=e.key(+)

in a big query, I can't put more than one (+) clause, or I get the 
out-of-space error.  I made a series of views like

create view1 as
select * 
from a, b,c
where a.a_key=b.b_key
and a.a_key=c.c_key(+)

create view2 as
select *
from view1, d, e
where a_key =d_key(+)
and a_key = e_key(+)

Usually I narrow down the fields as the last step with the query being

Select w, x, y, z 
from view2

This may result in a slower query with more data, but means I don't have 
to rebuild the views to add or exclude a line.

Is this pretty? no.
Should I have to do it? probably not.
But does it work? sure ;)

I feel almost like I am using MySQL in cases like this :(
note that I can build very complex queries, it's just the ones with (+) 
that seem to cause the problems.
Also note that my database isn't _remotely_ full, I have about 6 tables 
with a few thousand rows each, and the DB size is over 80MB.  None of the 
rows are huge or contain binary data.

Hopefully though, either the SAPDB team can tell us why certain queries 
take up so much space and how to rewrite them, or figure out where the 
problem in SAP is.

Thank you,
      Noah Silva
      IS&T - Programmer Analyst
      (215) 419 - 7916





"Zabach, Elke" <elke.zabach@stripped>
02/20/2003 10:16 AM

 
        To:     'JON' <jon@stripped>, sapdb.general@stripped
        cc: 
        Subject:        RE: Space for results table exhausted


JON wrote:

> 
> Peter,
> Thanks for the reply. First, I made a mistake in my example 
> in not including
> a field from the tblAOType in the output that would've shown 
> a need to use
> that table. Aside from that, I had already tried what you 
> suggested before
> writing with no change in the results. Here's a better example of the
> offending query with the most restricting condition first. (Using the
> Explain command renders that same result as before).

Just to point out:
changing the sequence of conditions will not change the number of 
(intermediate) resultrows produced --> it is not astonishing to see no 
change
in the behaviour.

And to build a view and to do the select on top of this view is an
idea, but none of us should believe that this will make things better
(it will make things even worse, I assume).

How many records are involved in these three tables and
how 'full' is your database?

Elke
SAP Labs Berlin

> 
> SELECT
> "OCPC"."TBLAOTYPE"."AOTYPE","OCPC"."TBLARCHOWNER"."NAME","OCPC
> "."TBLPROJAO".
> "PROJAO","OCPC"."TBLPROJAO"."SHOWONRESULTS","OCPC"."TBLARCHOWN
> ER"."PHONE","O
> CPC"."TBLARCHOWNER"."ZIP","OCPC"."TBLARCHOWNER"."STATE","OCPC"
> ."TBLARCHOWNER
> "."CITY","OCPC"."TBLARCHOWNER"."ADDR1"
> 
> FROM "OCPC"."TBLAOTYPE","OCPC"."TBLARCHOWNER","OCPC"."TBLPROJAO"
> 
> WHERE (("OCPC"."TBLPROJAO"."PROJID" = 8329) AND
> ("OCPC"."TBLPROJAO"."SHOWONRESULTS" = TRUE)) AND 
> "OCPC"."TBLARCHOWNER"."ID"=
> "OCPC"."TBLPROJAO"."ARCHOWNERID" (+) AND 
> "OCPC"."TBLAOTYPE"."AOTYPEID"=
> "OCPC"."TBLPROJAO"."AOTYPEID" (+)
> 
> ORDER BY "OCPC"."TBLPROJAO"."PROJAO" ASC
> 
> This is just one example of a query I build on the fly in 
> Visual Basic code.
> Building views seems unreasonable and it doesn't seem to me 
> that there are
> THAT many records involved here. But if I'm mistaken then by all means
> somebody straighten me out. :)
> 
> Thanks,
> Jon
> 
> 
> 
> "Peter Willadt" <Willadt@stripped> wrote in message
> news:200302200643.24717.willadt@ style="color:#666">stripped...
> > 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"."TBLARCHO
> WNER"."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
> 
> 
> 
> _______________________________________________
> sapdb.general mailing list
> sapdb.general@stripped
> http://listserv.sap.com/mailman/listinfo/sapdb.general
> 
_______________________________________________
sapdb.general mailing list
sapdb.general@stripped
http://listserv.sap.com/mailman/listinfo/sapdb.general