Mega Code Archive
Nested for loop vs table join in for loop
<![CDATA[
SQL>
SQL>
SQL> create table myTable1( a int primary key, y char(80) );
Table created.
SQL>
SQL> create table myTable2( b int primary key, a references myTable1, y char(80) );
Table created.
SQL>
SQL> create index myTable2_a_idx on myTable2(a);
Index created.
SQL>
SQL> create table myTable3( c int primary key, b references myTable2, y char(80) );
Table created.
SQL>
SQL> create index myTable3_b_idx on myTable3(b);
Index created.
SQL>
SQL> insert into myTable1
2 select rownum, 'x'
3 from all_objects
4 where rownum <= 1000;
1000 rows created.
SQL>
SQL> insert into myTable2
2 select rownum, mod(rownum,1000)+1, 'x'
3 from all_objects
4 where rownum <= 5000;
5000 rows created.
SQL>
SQL> insert into myTable3
2 select rownum, mod(rownum,5000)+1, 'x'
3 from all_objects;
12588 rows created.
SQL>
SQL> begin
2 for i in 1 .. 1000
3 loop
4 for x in ( select myTable1.a myTable1a, myTable1.y myTable1y,
5 myTable2.b myTable2b, myTable2.a myTable2a, myTable2.y myTable2y,
6 myTable3.c myTable3c, myTable3.b myTable3b, myTable3.y myTable3y
7 from myTable1, myTable2, myTable3
8 where myTable1.a = i
9 and myTable2.a (+) = myTable1.a
10 and myTable3.b (+) = myTable2.b )
11 loop
12 null;
13 end loop;
14 end loop;
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> begin
2 for i in 1 .. 1000
3 loop
4 for a in ( select myTable1.a, myTable1.y from myTable1 where myTable1.a = i )
5 loop
6 for b in ( select myTable2.b, myTable2.a, myTable2.y from myTable2 where myTable2.a = a.a )
7 loop
8 for c in ( select myTable3.c, myTable3.b, myTable3.y from myTable3 where myTable3.b = b.b )
9 loop
10 null;
11 end loop;
12 end loop;
13 end loop;
14 end loop;
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
]]>