Re: SQL Cost too high. Please help

Hi,

Can you show the two queries ?

Why do you think that the plan used by the optimizer is bad ?

Regards

amitabh_at_galaxycorp.com (Amitabh) wrote in message news:<63bd9bdd.0208272104.39e34fb0_at_posting.google.com>...
> Sorry, am reposting the query, as pervious one somehow got attached to
> existing thread.
>
> I am facing a problem with an SQL.
>
>
> One particular simple join (on UK with 7,000 rec) is increasing the
> cost drastically (~ 165759) without the join SQL cost is 1387.
>
> The only differene I find is with the with it has 3 HASH JOINS and
> without only one.
>
> Can anybody point me out what's happening?. What is HASH JOIN.
>
> Thanks in advance
>
> Amitabh
>
> Cost with Join:
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=165759 Card=58
> Bytes=33408)
> 1 0 FILTER
> 2 1 SORT (GROUP BY) (Cost=165759 Card=58 Bytes=33408)
> 3 2 FILTER
> 4 3 HASH JOIN (Cost=1018 Card=178563 Bytes=102852288)
> 5 4 TABLE ACCESS (FULL) OF 'COCOPHDR' (Cost=50
> Card=9276 Bytes=296832)
> 6 4 HASH JOIN (OUTER) (Cost=126 Card=1925
> Bytes=1047200)
> 7 6 NESTED LOOPS (OUTER) (Cost=93 Card=139
> Bytes=71168)
> 8 7 HASH JOIN (Cost=83 Card=10 Bytes=4800)
> 9 8 TABLE ACCESS (FULL) OF 'PE_ORIG_DEST_MSTR'
> (Cost=6 Card=70 Bytes=2240)
> 10 8 NESTED LOOPS (Cost=76 Card=14 Bytes=6272)
> 11 10 NESTED LOOPS (OUTER) (Cost=75 Card=1
> Bytes=416)
>
> Cost without join:
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1387 Card=1
> Bytes=537)
> 1 0 FILTER
> 2 1 SORT (GROUP BY) (Cost=1387 Card=1 Bytes=537)
> 3 2 FILTER
> 4 3 HASH JOIN (Cost=128 Card=2687 Bytes=1442919)
> 5 4 TABLE ACCESS (FULL) OF 'PE_ORIG_DEST_MSTR' (Cost=6
> Card=1385 Bytes=44320)
> 6 4 NESTED LOOPS (OUTER) (Cost=91 Card=194
> Bytes=97970)
> 7 6 NESTED LOOPS (OUTER) (Cost=77 Card=14
> Bytes=6622)
> 8 7 NESTED LOOPS (OUTER) (Cost=76 Card=1
> Bytes=441)
> 9 8 NESTED LOOPS (OUTER) (Cost=75 Card=1
> Bytes=407)
> 10 9 NESTED LOOPS (OUTER) (Cost=74 Card=1
> Bytes=373)
> 11 10 NESTED LOOPS (OUTER) (Cost=73 Card=1
> Bytes=339)
Received on Sat Sep 07 2002 - 10:28:41 CEST