Wednesday, June 11, 2014

Oracle PL/SQL Normal Insert and Bulk Insert


CREATE TABLE XXAA_INSERT_TEST (
  id           NUMBER(10),
  code         VARCHAR2(10),
  description  VARCHAR2(50));

ALTER TABLE XXAA_INSERT_TEST ADD (
  CONSTRAINT XXAA_INSERT_TEST_pk PRIMARY KEY (id));

ALTER TABLE XXAA_INSERT_TEST ADD (
  CONSTRAINT XXAA_INSERT_TEST_uk UNIQUE (code));

SET SERVEROUTPUT ON;

--------------------------------------------------------------------------------
DECLARE
  TYPE t_XXAA_INSERT_TEST_tab IS TABLE OF XXAA_INSERT_TEST%ROWTYPE;

  l_tab    t_XXAA_INSERT_TEST_tab := t_XXAA_INSERT_TEST_tab();
  l_start  NUMBER;
  l_size   NUMBER            := 10000;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. l_size
  LOOP
    l_tab.extend;
    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE XXAA_INSERT_TEST';

  -- Time regular inserts.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    INSERT INTO XXAA_INSERT_TEST (id, code, description)
    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' || (DBMS_UTILITY.get_time - l_start));
  EXECUTE IMMEDIATE 'TRUNCATE TABLE XXAA_INSERT_TEST';

  -- Time bulk inserts. 
  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO XXAA_INSERT_TEST VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' || (DBMS_UTILITY.get_time - l_start));

  COMMIT;

END;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect