> Well, we all see the hash join in the fisrt query while the second one is
> doing the nested loop and here is your time.
>
>  3309     HASH JOIN (cr=1523 r=0 w=0 time=223005 us)
>
> what about the memory allocate to the hash join is too small and the
> hash join is pushed to disk?

No,
the hash join is in memory:


14:59:08 SQL> r
1 select *
2        from (select a. *, rownum r
3         from (select distinct atp.part_number as codice,
4                        lsc.ds_lunga   as descr,
5                        ''         as TIPOOPT,
6                        ''         as b
7               from filtro_dati_catalogo fdc,
8                  an_telai         ati,
9                  vp_tavole        vpt,
10                  assoc_tavole_parts  atp,
11                  an_parts         ap,
12                  lessico_pn       lsc
13              where fdc.id_subcatalogo in (0) and ati.targa =
'FNZ8243' and
14                  lsc.language_code = 1 and
15                  fdc.pv_code = ati.pv_code and
16                  fdc.pv_code = vpt.pv_code and
17                  vpt.cod_tavola_grafica =
18                  atp.cod_tavola_grafica and
19                  ap.part_number = atp.part_number and
20                  lsc.codice_lessico = ap.codice_lessico and
21                  (lsc.ds_lunga like '1118647' or
22                  Trim(ap.part_number) like '1118647')
23              order by codice asc) a
24         where rownum <= 23) b
25*       where r > 0

Elapsed: 00:00:00.83

Statistics
----------------------------------------------------------
      0 recursive calls
      0 db block gets
    1725 consistent gets
      0 physical reads
      0 redo size
    758 bytes sent via SQL*Net to client
    656 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      1 sorts (memory)
      0 sorts (disk)
      1 rows processed

14:59:09 SQL> select *
14:59:15  2        from (select a. *, rownum r
14:59:15  3         from (select /*+ USE_NL(lsc ap) */ distinct
atp.part_number as codice,
14:59:15  4                        lsc.ds_lunga   as descr,
14:59:15  5                        ''         as TIPOOPT,
14:59:15  6                        ''         as b
14:59:15  7               from filtro_dati_catalogo fdc,
14:59:15  8                  an_telai         ati,
14:59:15  9                  vp_tavole        vpt,
14:59:15 10                  assoc_tavole_parts  atp,
14:59:15 11                  an_parts         ap,
14:59:15 12                  lessico_pn       lsc
14:59:15 13              where fdc.id_subcatalogo in (0) and
ati.targa = 'FNZ8243' and
14:59:15 14                  lsc.language_code = 1 and
14:59:15 15                  fdc.pv_code = ati.pv_code and
14:59:15 16                  fdc.pv_code = vpt.pv_code and
14:59:15 17                  vpt.cod_tavola_grafica =
14:59:15 18                  atp.cod_tavola_grafica and
14:59:15 19                  ap.part_number = atp.part_number and
14:59:15 20                  lsc.codice_lessico =
ap.codice_lessico and
14:59:15 21                  (lsc.ds_lunga like '1118647' or
14:59:15 22                  Trim(ap.part_number) like '1118647')
14:59:15 23              order by codice asc) a
14:59:15 24         where rownum <= 23) b
14:59:15 25       where r > 0;

Elapsed: 00:00:00.66

Statistics
----------------------------------------------------------
      0 recursive calls
      0 db block gets
    7559 consistent gets
      0 physical reads
      0 redo size
    758 bytes sent via SQL*Net to client
    656 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      1 sorts (memory)
      0 sorts (disk)
      1 rows processed


Dimitre

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