Wednesday, December 1, 2010

PL/SQL forall operator speeds for table inserts

Loading an Oracle table from a PL/SQL array involves expensive context switches, and the PL/SQL FORALL operator speed is amazing here is an example


create  table  t_dba_objects as  select * from dba_objects  where  1=2 ;

DECLARE

  TYPE prod_tab IS TABLE OF dba_objects%ROWTYPE;
  dba_objects_tab prod_tab := prod_tab();
  start_time number; end_time number;
BEGIN
  SELECT * BULK COLLECT INTO dba_objects_tab FROM dba_objects;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE t_dba_objects';
  Start_time := DBMS_UTILITY.get_time;
  FOR i in dba_objects_tab.first .. dba_objects_tab.last LOOP
    INSERT INTO t_dba_objects VALUES (
        dba_objects_tab(i).OWNER ,dba_objects_tab(i).OBJECT_NAME,
        dba_objects_tab(i).SUBOBJECT_NAME,dba_objects_tab(i).OBJECT_ID,
        dba_objects_tab(i).DATA_OBJECT_ID,dba_objects_tab(i).OBJECT_TYPE ,
       dba_objects_tab(i).CREATED ,dba_objects_tab(i).LAST_DDL_TIME,
       dba_objects_tab(i).TIMESTAMP ,dba_objects_tab(i).STATUS ,
       dba_objects_tab(i).TEMPORARY,dba_objects_tab(i).GENERATED ,
      dba_objects_tab(i).SECONDARY  );
  END LOOP;
  end_time := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Conventional Insert: '||to_char(end_time-start_time));
  EXECUTE IMMEDIATE 'TRUNCATE TABLE t_dba_objects';
  Start_time := DBMS_UTILITY.get_time;
  FORALL i in dba_objects_tab.first .. dba_objects_tab.last
   INSERT INTO t_dba_objects VALUES dba_objects_tab(i);
  end_time := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Bulk Insert: ' ||to_char(end_time-start_time));
  COMMIT;
END;
/
Conventional Insert: 23689

Bulk Insert: 128

Note:Forall   is very fast then the convential for loop but it uses much  more  UGA memory depend on row count and column count.(e.x for 100m table I  examine that it used  5g UGA memory)

No comments: