For loops or While loops to scan collections?

출처 : http://www.toadworld.com/Community/Blogs/tabid/67/EntryID/280/Default.aspx

Ihave generally recommended in the past that whenever you are writingcode to iterate through the elements of a collection, you should use awhile loop, combined with the FIRST-NEXT or LAST-PRIOR collectionmethods.
 

The key advantage of this approach is that the code will not raisea NO_DATA_FOUND exception if your collection is sparse (there is anindex value between FIRST and LAST that is not defined). And if yourcollection is empty, the loop will not execute at all, whereas with afor loop, an empty collection could cause a VALUE_ERROR exception ifyou are not careful.
 

Here is an example of the kind of code that requires the use of the while loop:

DECLARE
   TYPE employee_tt
   IS
      TABLE OF employees%ROWTYPE
         INDEXBY pls_integer; 

   employee_cache   employee_tt;
BEGIN
   /* Fill the collection using the employee ID
      as the index value - most like these values are
      NOT sequentially defined; primary keys can
      certainly have "gaps". */
   FOR rec IN (SELECT   *
                 FROM   employees)
   LOOP
      employee_cache(rec.employee_id):=rec;
   END LOOP; 

   FOR indx IN 1..employee_cache.COUNT
   LOOP
      DBMS_OUTPUT.put_line(employee_cache(indx).last_name);
   END LOOP;
END;
/

When I execute this block, I get the following error:
 

 

Instead, I should use a while loop:

DECLARE
   TYPE employee_tt
   IS
      TABLE OF employees%ROWTYPE
         INDEXBY pls_integer;

   employee_cache   employee_tt;
   l_index           pls_integer;
BEGIN
   /* Fill the collection using the employee ID
      as the index value - most like these values are
      NOT sequentially defined; primary keys can
      certainly have "gaps". */
   FOR rec IN (SELECT   *
                 FROM   employees)
   LOOP
      employee_cache(rec.employee_id):=rec;
   ENDLOOP;

   l_index:=employee_cache.FIRST;

   WHILE(l_index IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line(employee_cache(l_index).last_name);
      l_index:=employee_cache.NEXT(l_index);
   END LOOP;
END;
/

and then I will not see any errors.
 

So, no doubt about it, this is good advice – but should you always use the while loop?
 

I suggest that in situations when you know, when you areabsolutely sure, that your collection is densely-filled, you should usethe FOR loop, for two reasons:

• It is a simpler solution, requiring less code(reducing the chance of a bug creeping into your code and making iteasier to maintain).
• It is more self-documenting. Byusing the for loop construct, you are stating that an assumption ofthis code is that the collection is densely-filled.
• It is more efficient than the while loop.

So when you can be certain that your collection is densely-filled? When....

• The collection is populated by a BULK COLLECTquery. In this situation, the first index used is always 1, and thecollection is filled sequentially (2, 3, 4...) from that index.
• Thecollection is a nested table assigned its contents from a MULTISEToperator (UNION, INTERSECT and EXCEPT). These set level operatorsalways fill sequentially a nested table from index value 1.

And what kind of difference in performance can you expect to see? Not a very big difference, but the FOR loop is definitely faster. I put together the script below (relying on the sf_timer package, included in the demo.zip.

DECLARE
   l_index     pls_integer;
   l_source   DBMS_SQL.varchar2a;
BEGIN
   sf_timer.start_timer; 

   SELECT    text
   BULKCOLLECTINTO   l_source
   FROM    all_source; 

   sf_timer.show_elapsed_time(
      'Retrieved ' || TO_CHAR(l_source.COUNT) || ' elements'
   );
   --
   sf_timer.start_timer; 

   FOR indx IN 1..l_source.COUNT
   LOOP
      NULL;
   ENDLOOP; 

   sf_timer.show_elapsed_time('FOR loop through collection');
   --
   sf_timer.start_timer;
   l_index:=l_source.FIRST; 

   WHILE(l_index IS NOT NULL)
   LOOP
      NULL;
      l_index:=l_source.NEXT(l_index);
   END LOOP; 

   sf_timer.show_elapsed_time('Full collection scan with NEXT');
END;

I saw the following results:

Retrieved 3079394 elements - Elapsed CPU : 11.14 seconds.
FOR loop through collection - Elapsed CPU : .05 seconds.
Full collection scan with NEXT - Elapsed CPU : .48 seconds. 

In other words, the while loop is an order of magnitude slower  thanthe for loop. That sounds like a big deal, but it's only a differenceof .4 seconds with a scan of over 3,000,000 elements. With smallercollections, you'll probably never notice the difference.