Re: CBO - hash join vs nested loops

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Fri, 2 Dec 2005 18:37:20 +0100
Message-ID: <087a01c5f767$0d909b00$1a03310a@IBME1D11967173>

These are the execution plans. Thanks for the suggestion to verify if the estimation for the number of driving rows that the CBO expects from either table (based on the single table predicates) is correct.

I'm trying to figure out the reason I have this behaviour on 3 or 4 9ir2 instances, that I personally set up ...

18:25:46 SQL> select count(distinct fdc.cbv_code)
18:25:46   2  from filtro_dati_catalogo fdc
18:25:46   3  inner join opt_vp vo on fdc.id_subcatalogo in (0)
18:25:46   4  and vo.codice_opt = 29 and fdc.pv_code = vo.pv_code;

Elapsed: 00:00:00.01

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=428 Card=1 Bytes=46) 1 0 SORT (GROUP BY)

   2    1     HASH JOIN (Cost=428 Card=2076 Bytes=95496)
   3    2       INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_14178' (UNIQUE) (Co
          st=11 Card=1748 Bytes=33212)

   4    2       INDEX (FAST FULL SCAN) OF 'XIF01FILTRO_DATI_CATALOGO'
          (UNIQUE) (Cost=409 Card=424315 Bytes=11456505)




18:25:47 SQL> select /*+ USE_NL(fdc vo) */ count(distinct fdc.cbv_code)
18:25:54   2  from filtro_dati_catalogo fdc

18:25:54 3 inner join opt_vp vo on fdc.id_subcatalogo in (0) 18:25:54 4 and vo.codice_opt = 29 and fdc.pv_code = vo.pv_code; Elapsed: 00:00:00.00

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3507 Card=1 Bytes=46
          )

   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS (Cost=3507 Card=2076 Bytes=95496)
   3    2       INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_14178' (UNIQUE) (Co
          st=11 Card=1748 Bytes=33212)

   4    2       INDEX (RANGE SCAN) OF 'XIF02FILTRO_DATI_CATALOGO' (UNI
          QUE) (Cost=2 Card=1 Bytes=27)

18:25:55 SQL> Best regards,
Dimitre Radoulov

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 02 2005 - 11:39:30 CST