Message-Id: <10693.123004@fatcity.com>
From: Chuck Hamilton <chuck_hamilton@yahoo.com>
Date: Mon, 27 Nov 2000 13:18:49 -0800 (PST)
Subject: Why does hash join use TEMP tablespace?

--0-1714636915-975359929=:16718
Content-Type: text/plain; charset=us-ascii


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 shows 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.
--0-1714636915-975359929=:16718
Content-Type: text/html; charset=us-ascii

<P>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 shows no sorts in the explain plan need to use the temp tablespace?</P>
<P>Here's the query and explain plan.</P><FONT color=#0000f0 size=2><FONT face="Courier New">
<P>select</FONT><FONT color=#000000 size=2></FONT><BR><FONT face="Courier New">&nbsp; v</FONT></FONT><FONT face="Courier New"><FONT color=#0000f0 size=2>.*,</FONT><FONT color=#000000 size=2> p</FONT><FONT color=#0000f0 size=2>.</FONT><FONT color=#000000 size=2></FONT><FONT face="Courier New">person_org_id<BR></FONT></FONT><FONT face="Courier New"><FONT color=#0000f0 size=2>from</FONT><FONT color=#000000 size=2> </FONT><FONT color=#000000 size=2></FONT><BR><FONT face="Courier New">&nbsp; person p</FONT></FONT><FONT face="Courier New"><FONT color=#0000f0 size=2>, </FONT><FONT color=#000000 size=2>testload</FONT><FONT color=#0000f0 size=2>.</FONT><FONT color=#000000 size=2>vince_temp v</FONT><FONT color=#0000f0 size=2></FONT><BR><FONT face="Courier New">where<BR>&nbsp; </FONT></FONT><FONT face="Courier New"><FONT color=#000000 size=2>v</FONT><FONT color=#0000f0 size=2>.</FONT><FONT color=#000000 size=2>user_id </FONT><FONT color=#0000f0 size=2>=</FONT><FONT color=#000000 size=2> !
p</FONT><FONT color=#0000f0 size=2>.</FONT><FONT color=#000000 size=2>user_id</FONT><FONT color=#0000f0 size=2>(+)</P></FONT></FONT>
<P><FONT face="Courier New">SELECT STATEMENT Optimizer=CHOOSE (Cost=16686 Card=505507962 Bytes=61166463402)<BR>&nbsp; HASH JOIN (OUTER) (Cost=16686 Card=505507962 Bytes=61166463402)<BR>&nbsp;&nbsp;&nbsp; TABLE ACCESS (FULL) OF VINCE_TEMP (Cost=8031 Card=4321689 Bytes=432168900)<BR>&nbsp;&nbsp;&nbsp; TABLE ACCESS (FULL) OF PERSON (Cost=32 Card=11697 Bytes=245637)<BR></FONT></P><p><br><hr size=1><b>Do You Yahoo!?</b><br>
<a href="http://shopping.yahoo.com/">Yahoo! Shopping</a> - 
Thousands of Stores. Millions of Products.
--0-1714636915-975359929=:16718--