Re: How do you calcuate the temp space needed for view?

There are 2 issues here. First the error. What version of Oracle are you using? Is the TEMP tablespace set up as TEMPORARY? What block size are you using (impacts max extents on older versions of Oracle)? If you are not the only user on the system, you must also account for other sorts that are occuring at the same time.

Second, the calculation. The calculation you put forward would be reasonably accurate if you were performing a cartesian product. In the case of the view, how many rows match the criteria. If each table has 1,000,000 rows and the average row length is 1k, then each table consumes roughly 1,024,000,000 bytes. If we plug this into your formula.. (1024 * 1000000) * (1024 * 1000000) = 1024000000 * 1024000000 = 1048576000000000000 or 931 petabytes (1024 gig = 1 petabyte). (Note to self, buy EMC stock).

A more accurate method would be to determine how many records will be returned by the query (each table can be queried independently). For each record in table1(parent), how many records will be returned in table2(child)? Get an average, say that for each record in table1, an average of 2 records in table2 will be returned. At this point you need to reverse the numbers for the calculation. Why? Each child will 'attach' to its own copy of the parent record. So, take the size of table2 (# of rows * row length) and add it to the size of table1 (# of rows * row length) * the average number of children.

This calculation is not exact and does not account for overhead, hwm, etc. But I have used it in some data warehousing systems (back when disk was not so cheap) and it worked out reasonably well.

Dan Fink

CHAN Chor Ling Catherine (CSC) wrote:

>Hi Gurus,
>
>I issue the following command select count(*) from view1 and encounter the following error "ORA-01630: max # extents (121) reached in temp segment in tablespace TEMP". I think I need to increase the tablespace TEMP but how do I calculate the temporary space needed ?
>
>Assuming view1 is "select * from table1, table2 where table1.Col1=table2.Col1"
>Is the temporary space needed = (table 1 row-length * table 1 total number of rows ) * (table 2 row-length * table 2 total number of rows ) ?
>
>Any advice ? Thanks.
>
>Regds,
>New Bee
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).