well, they are one in the same. They are both running sql in a 'loop' - both comments 1 and 2 are identical above.


Now, any query that returns more than a single record is going to be processed in a loop - any query will be.

There are two types of queries in a 3gl (consider plsql a 3gl, it is).

a) those that are expected to return at least and at most one row. SELECT INTO is typically used if possible.

b) those that return 0 to N rows, where N is some number > 1. Any number >1, just greater than one.


So, let's skip (a) for a moment, they are uninteresting.

We'll use this in the example:

ops$tkyte%ORA10GR2> create table t as select * from all_objects where rownum <= 1000;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_monitor.session_trace_enable

PL/SQL procedure successfully completed.

We can efficiently process some queries like this:

ops$tkyte%ORA10GR2> begin
  2          for x in (select * from t)
  3          loop
  4                  null; -- process data....
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

in Oracle 10g and above, that will array fetch 100 records at a time from the query (silently, transparently, you need do nothing) and let you process them - if T has 1000 rows - that will context switch 1000/100 = 10 (plus one to find "no more data") times. That is perfectly OK.

SELECT * FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       11      0.00       0.00          0         24          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.01       0.01          0         25          0        1000

that shows what you would look for to verify this - 11 fetches, 1000 rows - array fetch size must have been 100. This is perfectly OK.


There will be times when your process involves updating the table you selected from or doing an update for each row you select out against some other table. There we would like the UPDATE (or delete or insert or whatever) to be batched up too. So, in plsql we'd explicitly array fetch into a client array, process the data and update back in bulk - it might look like this:

ops$tkyte%ORA10GR2> declare
  2          type vcArray is table of varchar2(200) index by binary_integer;
  3          type ridArray is table of rowid index by binary_integer;
  4
  5          l_object_names vcArray;
  6          l_rowids       ridArray;
  7
  8          cursor c is select rowid rid, object_name from t for update;
  9  begin
 10          open c;
 11          loop
 12                  fetch c bulk collect into l_rowids, l_object_names LIMIT 100;
 13
 14                  for i in 1 .. l_rowids.count
 15                  loop
 16                          l_object_names(i) := lower(l_object_names(i) );
 17                  end loop;
 18
 19                  forall i in 1 .. l_rowids.count
 20                          update t set object_name = l_object_names(i) where rowid = l_rowids(i);
 21
 22                  exit when c%notfound;
 23          end loop;
 24          close c;
 25  end;
 26  /

PL/SQL procedure successfully completed.

so in the tkprof we see the same fetches for the select:

SELECT ROWID RID, OBJECT_NAME FROM T FOR UPDATE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.01       0.01          0         15       1018           0
Fetch       11      0.00       0.00          0         24          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.01       0.02          0         40       1018        1000

and we can see the update was batched as well:

UPDATE T SET OBJECT_NAME = :B1 WHERE ROWID = :B2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.03       0.03          0       1000       1026        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.03       0.03          0       1000       1026        1000

so, 10 executes, 1000 updates - 100 rows per execute.


That shows how to do it - it is OK, normal, in fact impossible to avoid a loop to process data sometimes!!!!!!!


Now, what do I think they are talking about?

I think they mean "doing a do it yourself nested loop join"


For example, psuedo code:

for x in ( select * from t1 )
loop
   for y in ( select * from t2 where t2.col = X.COL )
   loop 
       for z in (select * from t3 where t3.col = Y.SOMETHING )
       loop

that would be *really bad*. As would:

for x in (select * from t )
loop
   select * into l_rec from t2 where t2.col = x.col;
   select * into l_rec2 from t3 where t3.col2 = x.col2;
   .....

There - in both cases - you meant to join.

When I see SQL inside of a SQL loop - then you probably have a problem.


Looping over sql - normal, expected, OK.

SQL looping over sql looping over sql .... - bad idea.