[Performance] cursor loop vs collection forall

 --建立暫存表格

CREATE TABLE cux_objects AS SELECT owner, object_name name, object_type type FROM all_objects WHERE 1 = 0 

--測試 1 (Cursor & Loop)  => about 40 sec

DECLARE

  vOwner      all_objects.owner%TYPE;

  vObjectName all_objects.object_name%TYPE;

  vObjectType all_objects.object_type%TYPE;

  vStart      DATE;

  vEnd        DATE;

  CURSOR curObject IS

    SELECT owner,

           object_name,

           object_type

    FROM   all_objects;

BEGIN

  vStart := SYSTIMESTAMP;

  OPEN curObject;

  LOOP

    FETCH curObject

      INTO vOwner,

           vObjectName,

           vObjectType;

    EXIT WHEN curObject%NOTFOUND;

    INSERT INTO cux_objects

    VALUES

      (vOwner,

       vObjectName,

       vObjectType);

  END LOOP;

  CLOSE curObject;

  COMMIT;

  vEnd := SYSTIMESTAMP;

  dbms_output.put_line((vEnd - vStart) * 86400);

END;

--測試 2 (Collection & forall)  => about 6 sec

DECLARE

  TYPE tOwner IS TABLE OF all_objects.owner%TYPE;

  TYPE tObjectName IS TABLE OF all_objects.object_name%TYPE;

  TYPE tObjectType IS TABLE OF all_objects.object_type%TYPE;

  vOwner      tOwner;

  vObjectName tObjectName;

  vObjectType tObjectType;

  vStart      DATE;

  vEnd        DATE;

BEGIN

  vStart := SYSTIMESTAMP;

  SELECT owner,

         object_name,

         object_type

  BULK   COLLECT

  INTO   vOwner,

         vObjectName,

         vObjectType

  FROM   all_objects;

  FORALL idx IN vObjectName.first .. vObjectName.last

    INSERT INTO cux_objects

    VALUES

      (vOwner(idx),

       vObjectName(idx),

       vObjectType(idx));

  COMMIT;

  

  vEnd := SYSTIMESTAMP;

  dbms_output.put_line ((vEnd - vStart) * 86400);

END;