Examples‎ > ‎

Nested Loops (Oracle)




             See this demo in  video 

Create some tables:

create table inner (id number);
create table outer (id number);
insert into inner select rownum from dba_objects where rownum < 100;
insert into outer select mod(rownum,100) from dba_objects where rownum < 10000;
create index inner_i on inner(id);

Code 1

declare
s integer:=0;
begin
  for o in (select id from outer )  loop
     for i in (select id from inner where id = o.id)  loop
        s:=s+1;
    end loop;
  end loop;
  dbms_output.put_line(s);
end; 
/

Code 2

SELECT /*+ USE_NL ( o i ) */
    COUNT (*)
FROM
    outer o,
    inner i
WHERE o.id = i.id;

Code 3

select /*+ USE_HASH ( o i ) */
        count(*) 
from outer o, inner i 
where o.id=i.id;

Load Editor

8 Users
100 Executions
No Sleep between Executions

Profile - start
Load Editor - kick off
lots of CPU usage and  cache buffers chains contention



click under "Events" the "latch: cache buffers chains" and on the details tab
click on "Analysis" to get more information



We see that the "latch: cache buffers chains" comes from a hot block and if 
there is a nested loops join then an index on the inner table can be the hot spot.
Address by trying a different join method such as hash join. Well the code written
in PLSQL forces the DBMS to do a type of nest join. We'd have to re-write the code
in a single SQL statemement to benefit from the optimizers ability to use a hash join:
Try

SELECT
    COUNT (*)
FROM
    outer o,
    inner i
WHERE o.id = i.id;

Run the same load but with this select statement instead:




              The small green bar on the right is the same load !


Comments