FreeLists / oracle-l / Re: CBO - hash join vs nested loops

["Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>]

I'm missing somethink. I answered too quickly, without verifing the result. The 
NL access choosen does the same lio as the hash join and the hinted version is 
always different:


20:05:24 SQL> select count(distinct fdc.cbv_code)
20:05: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:05:33   3  20:05:33   4  ;

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

Elapsed: 00:00:00.93

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=409 Card=1 Bytes=46)
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS (Cost=409 Card=2076 Bytes=95496)
   3    2       INDEX (FAST FULL SCAN) OF 'XIF01FILTRO_DATI_CATALOGO'
          (UNIQUE) (Cost=409 Card=424315 Bytes=11456505)

   4    2       INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_14178' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4276  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

20:05:35 SQL> select /*+ USE_NL(fdc vo) */ count(distinct fdc.cbv_code)
20:05:44   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:05:44   3  20:05:44   4  ;

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

Elapsed: 00:00:00.56

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=409 Card=1 Bytes=46)
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS (Cost=409 Card=2076 Bytes=95496)
   3    2       INDEX (FAST FULL SCAN) OF 'XIF01FILTRO_DATI_CATALOGO'
          (UNIQUE) (Cost=409 Card=424315 Bytes=11456505)

   4    2       INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_14178' (UNIQUE)




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

20:05:46 SQL> alter session set optimizer_index_caching=10;

Session altered.

Elapsed: 00:00:00.00
20:05:52 SQL> select count(distinct fdc.cbv_code)
20:06:05   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:06:05   3  20:06:05   4  ;

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

Elapsed: 00:00:00.94

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=409 Card=1 Bytes=46)
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS (Cost=409 Card=2076 Bytes=95496)
   3    2       INDEX (FAST FULL SCAN) OF 'XIF01FILTRO_DATI_CATALOGO'
          (UNIQUE) (Cost=409 Card=424315 Bytes=11456505)

   4    2       INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_14178' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4276  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



Regards,
Dimitre Radoulov