PL/SQL Bulk Insert
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PL/SQL Bulk Insert

  1. #1
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    Hi! All
    I have a PL/SQL table of REcords. Can I do a bulk insert (i.e. a single insert, whichis faster) from the PL/SQL table to the database table?
    Regards
    Sudip

  2. #2
    Join Date
    Feb 2001
    Posts
    125
    Hope given below of Oracle Documentation will help you.

    How Do Bulk Binds Improve Performance?
    The assigning of values to PL/SQL variables in SQL statements is called binding. The binding of an entire collection at once is called bulk binding. Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth. For example, the following DELETE statement is sent to the SQL engine just once, with an entire nested table:

    DECLARE
    TYPE NumList IS TABLE OF NUMBER;
    mgrs NumList := NumList(7566, 7782, ...); -- manager numbers
    BEGIN
    ...
    FORALL i IN mgrs.FIRST..mgrs.LAST
    DELETE FROM emp WHERE mgr = mgrs(i);
    END;


    In the example below, 5000 part numbers and names are loaded into index-by tables. Then, all table elements are inserted into a database table twice. First, they are inserted using a FOR loop, which completes in 38 seconds. Then, they are bulk-inserted using a FORALL statement, which completes in only 3 seconds.

    SQL> SET SERVEROUTPUT ON
    SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

    Table created.

    SQL> GET test.sql
    1 DECLARE
    2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
    3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
    4 pnums NumTab;
    5 pnames NameTab;
    6 t1 CHAR(5);
    7 t2 CHAR(5);
    8 t3 CHAR(5);
    9 PROCEDURE get_time (t OUT NUMBER) IS
    10 BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;
    11 BEGIN
    12 FOR j IN 1..5000 LOOP -- load index-by tables
    13 pnums(j) := j;
    14 pnames(j) := 'Part No. ' || TO_CHAR(j); 15 END LOOP;
    16 get_time(t1);
    17 FOR i IN 1..5000 LOOP -- use FOR loop
    18 INSERT INTO parts VALUES (pnums(i), pnames(i));
    19 END LOOP;
    20 get_time(t2);
    21 FORALL i IN 1..5000 -- use FORALL statement
    22 INSERT INTO parts VALUES (pnums(i), pnames(i));
    23 get_time(t3);
    24 DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
    25 DBMS_OUTPUT.PUT_LINE('---------------------');
    26 DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));
    27 DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR(t3 - t2));
    28* END;
    SQL> /
    Execution Time (secs)
    ---------------------
    FOR loop: 38
    FORALL: 3


    P.Soni


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width