Re: CBO - hash join vs nested loops

So, this is the result:

SQL> select count(*)
  2 from filtro_dati_catalogo
where id_subcatalogo in (0); 3

  COUNT(*)

    423077

SQL> select count(*)
  2 from opt_vp
where codice_opt = 29; 3

  COUNT(*)

      1533

And, of course, as Jonathan suggested, it was the index caching that Oracle doesn't take into consideration:

SQL> set autot traceo exp
SQL> select count(distinct fdc.cbv_code)   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_code 3 4 ;

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)

SQL> alter session set optimizer_index_caching=10;

Session altered.

SQL> select count(distinct fdc.cbv_code)   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_code 3 4 ;

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)

Thank you for the valuable input. I have to check the other problematic statements and then eventually review the settings of OIC.

Best regards,
Dimitre Radoulov

P.S. I live in Italy. It's the database of the online catalog of a well known manufacturer in the transport sector :O).

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