tempspace estimation for a query

For a report, I have a query that needs a lot of temporary space, due to sortings and joins. Issuing 'onstat -d' every 30 seconds allows me to watch the tempspace as it is continously decreasing, until no more is available. At that point I get some error message, and the tempspace is freed. I tried to add more tempspace..and more tempspace, still it keeps failing due to insufficient temp space :( Is there some way to estimate the space needed by a query to do its internal operations? Obviously within some range, but still it would be really usefull to know whether a query needs around 10g tempspace, 20 or 100. On the other hand, why shouldn't that be possible?

get the explain from the query. maybe you can add a couple of indexes??? saving sort space ??? or has joins or ... maybe there is a cartsesian product in your query??? Superboer. On 20 feb, 16:56, "xls" <dan.to...@gmail.com> wrote: > For a report, I have a query that needs a lot of temporary space, due > to sortings and joins. Issuing 'onstat -d' every 30 seconds allows me > to watch the tempspace as it is continously decreasing, until no more > is available. At that point I get some error message, and the > tempspace is freed. > I tried to add more tempspace..and more tempspace, still it keeps > failing due to insufficient temp space :( > Is there some way to estimate the space needed by a query to do its > internal operations? Obviously within some range, but still it would > be really usefull to know whether a query needs around 10g tempspace, > 20 or 100. > On the other hand, why shouldn't that be possible?

The query is basically a SELECT from a view which is created to look like a de-normalized version of a fat table: QUERY: ------ create view "informix".v_rptseldet (ideventrecord,idorglevel1,szfullname,idextension,szextension,szoriginating,szterminating,idcalltype,szcalltype,dtcallstart,idurationmsec,irateddurationmsec,imeterpulses,iratedpulses,szbillableamount,sztaxes,idlocation,szbillingname,szaccountcode,szaname,szbname) as select x0.ideventrecord ,x0.idorglevel1 ,x4.szfullname ,x0.idextension ,x2.szextension ,x0.szoriginating ,x0.szterminating ,x0.idcalltype ,x1.szcalltype ,x0.dtcallstart ,x0.idurationmsec ,x0.irateddurationmsec ,x0.imeterpulses ,x0.iratedpulses ,"informix".sp_numtostring(x0.cubillableamount ),"informix".sp_numtostring(x0.cutaxes ),x0.idlocation ,x3.szbillingname ,x0.szaccountcode ,x0.szaname ,x0.szbname from "informix".eventrecord x0 ,outer("informix".calltype x1 ) ,outer("informix".extension x2 ) ,outer("informix".location x3 ) ,outer("informix".orglevel1 x4 ) where ((((x1.idcalltype = x0.idcalltype ) AND (x0.idextension = x2.idextension ) ) AND (x0.idlocation = x3.idlocation ) ) AND (x0.idorglevel1 = x4.idorglevel1 ) ); Estimated Cost: 31897942 Estimated # of Rows Returned: 7397573 The big table is eventrecord, the other are smaller. If I create a table using the underlying query of the view above, it would take more space than eventrecord currenly does, but would have the same number of rows. All column names starting with 'id' have indexes. I also ran 'update statistics'. Still...is there a way to estimate the needed tempspace by analyzing the query and the tables?