L’algorithme du Nested Loop change en 11g. Je n’en dis pas plus :

Mes tables :

create table gark
(id1 number not null,
id2 number not null,
id3 number not null);

begin
for i in 1..100000 loop
insert into gark(id1, id2, id3)
values (i, i, i);
end loop;
commit;
end;
/

create unique index gark_idx on gark(id1, id3);

begin
dbms_stats.gather_table_stats(
user,
'GARK',
cascade=>true,
estimate_percent=>100,
method_opt=>'FOR ALL COLUMNS SIZE 254',
no_invalidate=> false);
end;
/

En 10g :

set autotrace traceonly

select /*+ use_nl(A B) */count(a.id3)
from gark a, gark b
where a.id1=b.id2;


Execution Plan
----------------------------------------------------------
Plan hash value: 3137705415

--------------------------------------------------------
| Id  | Operation           | Name     | Rows  |  Cost |
--------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |  100K |
|   1 |  SORT AGGREGATE     |          |     1 |       |
|   2 |   NESTED LOOPS      |          |   100K|  100K |
|   3 |    TABLE ACCESS FULL| GARK     |   100K|    65 |
|*  4 |    INDEX RANGE SCAN | GARK_IDX |     1 |     1 |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."ID1"="B"."ID2")


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

En 11g :

set autotrace traceonly

select /*+ use_nl(A B) */count(a.id3)
from gark a, gark b
where a.id1=b.id2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3137705415

-------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Cost |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 | 100K |
|   1 |  SORT AGGREGATE     |          |     1 |      |
|   2 |   NESTED LOOPS      |          |   100K| 100K |
|   3 |    TABLE ACCESS FULL| GARK     |   100K|  105 |
|*  4 |    INDEX RANGE SCAN | GARK_IDX |     1 |   11 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."ID1"="B"."ID2")


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

C’est énorme ! Essayez d’appréhender l’impact sur vos applications de ce changement d’algorithme du Nested Loop d’0racle 11g ou au lieu d’aller chercher la données pour une ligne, il va en chercher plusieurs à la fois…

-GarK!