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