Re: ORA-03232 and HASH_JOIN

From: Yuri McPhedran <esiyuri_at_my-deja.com>
Date: Fri, 01 Sep 2000 16:24:49 GMT
Message-ID: <8ool7n$spd$1@nnrp1.deja.com>

Hi Yong Huang,

> I wonder if it's simply because your tablespace is seriously
> fragmented even though the total free space is still large.

No, there should be no problem allocating a mere 11 blocks!

> look at your tablespace NEXT extent size.

It would be nice if the tablespace name had been shown instead of number "3"! I don't think this would fix the problem anyway, though I must admit that I didn't actually try it.

> A hash join does not do sorting.

That's what I thought.

> It may help if you can post the execution plan.

OK, here is the SQL statement and the plan. I have had to edit it to remove the table and column names as these are commercially sensitive. It should still be understandable though.

select m.COL_X, p.COL_Y, c.COL_Z
from TAB_A l, TAB_B m, TAB_C p, TAB_C c,

TAB_D pd, TAB_D cd

where m.COL_M=l.COL_N
and   p.COL_O=m.COL_P
and   c.COL_O=m.COL_P
and   p.COL_R=pd.COL_U
and   c.COL_R=cd.COL_U

and pd.COL_S=l.COL_T
and cd.COL_S=l.COL_T

SELECT STATEMENT Optimizer=CHOOSE (Cost=547 Card=3 Bytes=189) HASH JOIN (Cost=547 Card=3 Bytes=189)
HASH JOIN (Cost=419 Card=295 Bytes=15635)

      HASH JOIN (Cost=408 Card=295 Bytes=13275)
        TABLE ACCESS (FULL) OF TAB_A (Cost=16 Card=30900 Bytes=494400)
        HASH JOIN (Cost=196 Card=266678 Bytes=7733662)
          TABLE ACCESS (FULL) OF TAB_D (Cost=9 Card=2775 Bytes=30525)
          HASH JOIN (Cost=173 Card=266678 Bytes=4800204)
            TABLE ACCESS (FULL) OF TAB_D (Cost=10 Card=9665 Bytes=77320)
            TABLE ACCESS (FULL) OF TAB_C (Cost=125 Card=266678
Bytes=2666780)
      TABLE ACCESS (FULL) OF TAB_D (Cost=10 Card=9665 Bytes=77320)

TABLE ACCESS (FULL) OF TAB_C (Cost=125 Card=266678 Bytes=2666780)

--
Regards
Yuri McPhedran


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Sep 01 2000 - 11:24:49 CDT

Re: ORA-03232 and HASH_JOIN_ENABLED