Estimating how much temp space a query will take?
raindogAug 26 2011 — edited Aug 26 2011I have a query that is "SELECT * FROM some_table ORDER BY field_name DESC". some_table has an avg_row_len of 458 bytes and stats are current. There are just about 6 million rows in some_table.
TEMP is set to 500MB and the query fails for lack of TEMP space. I show about 176MB of TEMP is presently in use, so worst case I should have 324MB free.
So which calculation is correct for how much TEMP space is needed:
(a) 458 avg_row_len * 6,000,000 = about 3GB of space (and DBA_SEGMENTS agrees with this rough math). That's assuming it puts the whole row into the sort.
(b) 6,000,000 rows * 4 bytes for a ROWID (I think they're 4 bytes) = 22MB. That's assuming it sorts just a bunch of pointers to rows (which is how I thought it would work).