Re: CBO - hash join vs nested loops

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 02 2005 - 13:19:32 CST