[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sapdb-general
Subject:    Re: Space for result tables exhausted
From:       "Matteo Gattoni" <matteo.gattoni () icteam ! it>
Date:       2004-09-28 14:28:44
Message-ID: 01c201c4a567$756f75f0$9200000a () pc ! company ! icteam ! it
[Download RAW message or body]

Thanks a lot, Elke & Martin,
    the query now runs properly.
However, What is the amount of free space needed, in theory?

Thanks,
   Matteo

----- Original Message ----- 
From: "Zabach, Elke" <elke.zabach@sap.com>
To: "'Matteo Gattoni'" <matteo.gattoni@icteam.it>; <maxdb@lists.mysql.com>
Sent: Tuesday, September 28, 2004 4:11 PM
Subject: AW: Space for result tables exhausted


> Matteo Gattoni wrote:
> >
> > Hello,
> >   removing the clause ORDER BY from the query, everything goes.
> > But I need the ORDER BY clause.
> >
> > What I need to do so?
> > Bye,
> >    Matteo
>
> Now you have
> group by p.description, p.brand,p.category,t.year
> order by p.category, p.description, p.brand
>
> Why don't you say
> Group by p.category, p.description, p.brand, t.year
> -->using the same sequence as in the old order by and do NOT add an ORDER
BY
>
> That will help
>
> Elke
> SAP Labs Berlin
>
> >
> > ----- Original Message -----
> > From: "Matteo Gattoni" <matteo.gattoni@icteam.it>
> > To: <maxdb@lists.mysql.com>
> > Sent: Monday, September 27, 2004 1:06 PM
> > Subject: Space for result tables exhausted
> >
> >
> > Hello,
> >
> >   I have a Datawarehouse with only one fact table and three dimensional
> > tables. The fact table (name SALES_FACT) has 1297664 records. The
> > dimensional tables has respectivly 4 (STORE_DIMENSION), 1096
> > (TIME_DIMENSION) and 296 (PRODUCT_DIMENSION) records.
> >
> > Trying to execute a query like this
> >
> > select sum(sa.units_sold) unti_sold, sum(sa.dollars_sold) Ricavato,
> > sum(sa.dollars_cost) Costo, sum(sa.dollars_sold-dollars_cost) Guadagno,
> > p.description, p.brand,p.category,t.year from product_dimension p inner
> > join
> > sales_fact sa on p.product_key=sa.product_key
> > inner join time_dimension t on sa.time_key=t.time_key
> > group by p.description, p.brand,p.category,t.year
> > order by p.category, p.description, p.brand
> > I receive the following error
> >
> > General error;-904 POS(1) Space for result tables exhausted.
> >
> > The execution plane of the query is:
> >
> > OWNER        TABLENAME              COLUMN_OR_INDEX    STRATEGY
> > PAGECOUNT
> >                     T
> > TABLE SCAN                                            7
> >                     SA                                TIME_KEY
> > JOIN VIA RANGE                                       14656
> >                     P                                  PRODUCT_KEY
> > JOIN VIA KEY COLUMN                             3
> >
> > NO TEMPORARY RESULTS CREATED
> > INTERNAL    TEMPORARY RESULT                                    TABLE
SCAN
> > 500
> > INTERNAL    TEMPORARY RESULT                                    TABLE
SCAN
> > 500
> >
> > RESULT IS COPIED, COSTVALUE IS        226634
> > DB parameters are:
> > JOIN_SEARCH_LEVEL = 0
> > OPTIM_INV_ONLY = NO
> > OPTIM_MAX_MERGE =  500  (tried even with 1000)
> > JOIN_OPERATOR_IMPLEMENTATION = YES (tried tiwh NO too)
> > JOIN_TABLEBUFFER = 2560 (tried with 640, 1240)
> >
> > Data area is composed by 7 volumes (each with 102.400KB): used area
> > 306.816KB (43%).
> >
> > I see with other queries that, increasing the Data area adding volumes,
> > the
> > error disappears.
> > Is this the only way to solve the problem? If yes, how would be the free
> > space in theory?
> > Other possibilities?
> >
> > Thanks,
> >   Matteo
> >
> >
> > --
> > MaxDB Discussion Mailing List
> > For list archives: http://lists.mysql.com/maxdb
> > To unsubscribe:
http://lists.mysql.com/maxdb?unsub=elke.zabach@sap.com
>
> -- 
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
http://lists.mysql.com/maxdb?unsub=matteo.gattoni@icteam.it
>
>


-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=sapdb-general@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]