Re: CBO - hash join vs nested loops

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Fri, 2 Dec 2005 20:08:47 +0100
Message-ID: <005401c5f773$d441d0f0$4900a8c0@IBME1D11967173>

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

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

Received on Fri Dec 02 2005 - 13:11:08 CST