These are the execution plans. Thanks for the suggestion to verify if the
estimation for the number of driving rows that the CBO
expects from either table (based on the single table predicates) is correct.
I'm trying to figure out the reason I have this behaviour on 3 or 4 9ir2
instances, that I personally set up ...
18:25:46 SQL> select count(distinct fdc.cbv_code) 18:25:46 2 from filtro_dati_catalogo fdc 18:25:46 3 inner join opt_vp vo on fdc.id_subcatalogo in (0) 18:25:46 4 and vo.codice_opt = 29 and fdc.pv_code = vo.pv_code; Elapsed: 00:00:00.01
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)
18:25:47 SQL> select /*+ USE_NL(fdc vo) */ count(distinct fdc.cbv_code) 18:25:54 2 from filtro_dati_catalogo fdc 18:25:54 3 inner join opt_vp vo on fdc.id_subcatalogo in (0) 18:25:54 4 and vo.codice_opt = 29 and fdc.pv_code = vo.pv_code; Elapsed: 00:00:00.00
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)
18:25:55 SQL>
Best regards, Dimitre Radoulov
-- //www.freelists.org/webpage/oracle-l
