RE: Why does hash join use TEMP tablespace?

Every SQL statement that is being performed, depending on the amount of = data
& DB kernel configurations, needs to allocate temporary segments. If = you've
got plenty of memory free in your SGA you can avoid less activity in = your
temp tablespace defined to your user. If not, db engine will need to = handle
temporary allocation issues onto your tablespace, which implies I/O = access.
Therefore, you must declare more space to be considered in your temp tablespace definitions, otherwise you'll get space errors.
=A0

--
Jordi S.

----Original Message-----
From: Chuck Hamilton [ mailto:chuck_hamilton_at_yahoo.com
<mailto:chuck_hamilton_at_yahoo.com> ]
Sent: Monday, November 27, 2000 04:20 PM
To: Multiple recipients of list ORACLE-L
Subject: Why does hash join use TEMP tablespace?



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


=A0 v.*, p.person_org_id

from=20


=A0 person p, testload.vince_temp v

where


=A0 v.user_id =3D ! ! p.user_id(+)


SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D16686 Card=3D505507962
Bytes=3D61166463402)


=A0 HASH JOIN (OUTER) (Cost=3D16686 Card=3D505507962 =

Bytes=3D61166463402)


=A0=A0=A0 TABLE ACCESS (FULL) OF VINCE_TEMP (Cost=3D8031 Card=3D4321689

Bytes=3D432168900)


=A0=A0=A0 TABLE ACCESS (FULL) OF PERSON (Cost=3D32 Card=3D11697 =

Bytes=3D245637)







=A0 _____ =A0


Do You Yahoo!?
Yahoo!  <http://shopping.yahoo.com/> Shopping - Thousands of Stores.