Re: CBO - hash join vs nested loops

  • From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
  • To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>, "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Fri, 2 Dec 2005 20:17:06 +0100

I really missed somethin*G* with the copy/paste. This is the hinted access path 
with optimizer_index_caching = 0:

20:14:21 SQL> select /*+ USE_NL(fdc vo) */ count(distinct fdc.cbv_code)
20:14:33   2  from filtro_dati_catalogo fdc
inner join opt_vp vo on fdc.id_subcatalogo in (0)
and vo.codice_opt = 29 and fdc.pv_code = vo.pv_code20:14:33   3  20:14:33   4  ;

COUNT(DISTINCTFDC.CBV_CODE)
---------------------------
                         29

Elapsed: 00:00:00.56

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)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3088  consistent gets
          0  physical reads
          0  redo size
        510  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed



Dimitre Radoulov

Other related posts: