Re: Why does hash join use TEMP tablespace?

From: yong huang <yong321_at_yahoo.com>
Date: Tue, 28 Nov 2000 15:43:18 -0800 (PST)
Message-Id: <10694.123139@fatcity.com>

Hi, Chuck,

My understanding is that if you don't have a large enough HASH_AREA_SIZE, the hashing table is created in temporary segments in the TEMP tablespace. Temporary segments are created not only for sorting. Hash table creation, index creation / rebuild etc. all use them.

The hash area in memory is in your PGA in dedicated server configuration, SGA in MTS.

Yong Huang
yong321_at_yahoo.com

you wrote:

I have a query that's doing an outter hash join and fails with "ORA-1652: unable
 to extent temp segment by 1280 in tablespace TEMP". Why would a query that show
s no sorts in the explain plan need to use the temp tablespace?

Here's the query and explain plan.

select v.*, p.person_org_id
from person p, testload.vince_temp v
where v.user_id = p.user_id(+)

SELECT STATEMENT Optimizer=CHOOSE (Cost=16686 Card=505507962 Bytes=61166463402)   HASH JOIN (OUTER) (Cost=16686 Card=505507962 Bytes=61166463402)     TABLE ACCESS (FULL) OF VINCE_TEMP (Cost=8031 Card=4321689 Bytes=432168900)     TABLE ACCESS (FULL) OF PERSON (Cost=32 Card=11697 Bytes=245637)

---

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. Received on Tue Nov 28 2000 - 17:43:18 CST