WHAT IS BULK COLLECT & FORALL

• BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
  => Started using BULK COLLECT whenever I need to fetch large volumes of data.
  (Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.)
  
  Let it be thought I need to retrieve all the rows from the employees table and then perform some compensation analysis on each row. I can use BULK COLLECT as follows:
  
  

  PROCEDURE process_all_rows
  IS
     TYPE employees_aat 
     IS TABLE OF employees%ROWTYPE
        INDEX BY PLS_INTEGER;
     l_employees employees_aat;
  BEGIN
     SELECT *
     BULK COLLECT INTO l_employees
        FROM employees;
       
     FOR indx IN 1 .. l_employees.COUNT 
     LOOP
         analyze_compensation 
        (l_employees(indx));
     END LOOP;
  END process_all_rows; 

  
  
   => Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.
  
  
   
• FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly