strange execution plan

Hi,

we are using Oracle 8.1.7 on W2k ( 2 CPUs, 3 GB Ram) and following statement

SELECT

		bpf.PF_ID, ku.KUNDENNR, ku.KUNDENTYP, ku.BONITAET_ID,
      mbra.ID, ku.RVB_ID, bpf.LAUFZEIT_ID, bpf.WAEHRUNGS_ID ,
      sum(bpf.summe_brutto) AS BRUTTO,
      sum(bpf.SUMME_BRUTTO - (case when sw.WERT_GCPM is null then 0
else sw.WERT_GCPM end))
      AS NETTO, sum(sw.WERT_GCPM) AS SICHWERT,
      ku.ku_txt_01, ku.ku_txt_02, ku.ku_txt_03, ku.ku_txt_04,
		ku.ku_txt_05, ku.ku_txt_06, ku.ku_txt_07, ku.ku_txt_08,
ku.ku_txt_09,
		ku.ku_txt_10, ku.ku_txt_11, ku.ku_txt_12, ku.ku_txt_13  
FROM 
		KUNDE ku, MASTERBRANCHE mbra,
		KU_RVB_BRANCHE kurvbbra, HIERDIMBEZ1 hbra ,
		BASIS_GCPM bpf, SICHWERT_GCPM sw 
WHERE 
		bpf.waehrungs_id = sw.waehrungs_id (+)
		and bpf.sz_schluessel = sw.sz_schluessel (+) 
		AND  bpf.laufzeit_id = sw.laufzeit_id (+)
		and sw.flagaktuell = 1
		and sw.zweckerkl = 'T'
		and (kurvbbra.BEZUG_ID = ku.ID)
		AND (kurvbbra.FLAG = 'K')  AND (hbra.ID = kurvbbra.BRANCHE_ID)
		AND (hbra.DIM_BEZK = mbra.BRANCHEN_NR) AND
		(kurvbbra.LAND_ISO = mbra.LAND_ISO) AND (bpf.VKUNDEN_ID = ku.ID)
GROUP BY 
		bpf.PF_ID, ku.kundennr, ku.kundentyp, ku.bonitaet_id,
      mbra.id, ku.rvb_id, bpf.laufzeit_id, bpf.waehrungs_id,
      ku.ku_txt_01, ku.ku_txt_02, ku.ku_txt_03, ku.ku_txt_04,
		ku.ku_txt_05, ku.ku_txt_06, ku.ku_txt_07, ku.ku_txt_08,
ku.ku_txt_09,
		ku.ku_txt_10, ku.ku_txt_11, ku.ku_txt_12, ku.ku_txt_13 
ORDER BY 
		ku.kundennr

The plan therefor is

   0 ° SELECT STATEMENT Optimizer=CHOOSE (Cost=2175415251882 Card=2
°

          2119102505655 Bytes=4268986783591420)

   1 0 SORT (ORDER BY) (Cost=2175415251882 Card=22119102505655 By
°

          tes=4268986783591420)

   2 1 SORT (GROUP BY) (Cost=2175415251882 Card=22119102505655
°

          Bytes=4268986783591420)

   3 2 NESTED LOOPS (Cost=243466 Card=22119102505655 Bytes=42
°

          68986783591420)

   4 3 HASH JOIN (Cost=243466 Card=1065597742 Bytes=1832828
°

          11624)

   5 4 TABLE ACCESS (FULL) OF 'MASTERBRANCHE' (Cost=13 Ca
°

          rd=14598 Bytes=467136)

   6 4 HASH JOIN (Cost=3462 Card=729961462 Bytes=10219460
°

          4680)

   7 6 TABLE ACCESS (FULL) OF 'BASIS_GCPM' (Cost=191 Ca
°

          rd=289128 Bytes=10119480)

   8 6 HASH JOIN (Cost=3006 Card=252470 Bytes=26509350)
°

   9 8 INDEX (FULL SCAN) OF 'X_HIERDIMBEZ1_1' (NON-UN
°

          IQUE) (Cost=1 Card=152 Bytes=912)

  10 8 HASH JOIN (Cost=3004 Card=252470 Bytes=2499453
°

          0)

  11   10                 TABLE ACCESS (FULL) OF 'KU_RVB_BRANCHE' (Cos


°

          t=92 Card=252470 Bytes=2777170)

  12   10                 TABLE ACCESS (FULL) OF 'KUNDE' (Cost=2388 Ca


°

          rd=1620048 Bytes=142564224)

  13    3         TABLE ACCESS (BY INDEX ROWID) OF 'SICHWERT_GCPM'    


°

  14   13           INDEX (RANGE SCAN) OF 'XSICHWERTGCPM1' (NON-UNIQUE


°

          )

The thing i dont understand ist, why are there so high cards and so many galaxie dollars (costs)

Freddy Received on Thu Aug 05 2004 - 03:52:18 CDT