Tuesday, February 16, 2016

Oracle PL/SQL Bulk Bind

Bulk Bind:
  Improves the performance of oracle while manipulating data in Loops in Pl/sql.
  It can be implemented with For all Construct in Pl/SQL.

Example :
The time taken to insert, update then delete 10,000 rows using regular FOR..LOOP statements is approximately 34 seconds on my test server:

CREATE TABLE test1(  id    NUMBER(10) primary key,  description  VARCHAR2(50));

The time taken to insert, update then delete 10,000 rows using regular FOR..LOOP statements is approximately 34 seconds on my test server:
DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  t_id           id_type     :=  id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    t_id(t_id.last)   :=  i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
    FOR i IN t_id.first .. t_id.last LOOP
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
  END LOOP;
  FOR i IN t_id.first .. t_id.last LOOP
    UPDATE test1   SET    description = t_description(i)
    WHERE  id = t_id(i);
  END LOOP;
  FOR i IN t_id.first .. t_id.last LOOP
    DELETE test1 WHERE  id = t_id(i);
  END LOOP;
Commit;
End;

Using the FORALL construct to bulk bind the inserts this time is reduced to 18 seconds:
DECLARE
  TYPE id_type  IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  t_id           id_type          :=  id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    t_id(t_id.last) := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
    FORALL i IN t_id.first .. t_id.last
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
    FORALL i IN t_id.first .. t_id.last
    UPDATE test1  SET    description = t_description(i)
    WHERE  id = t_id(i);
   FORALL i IN t_id.first .. t_id.last
    DELETE test1 WHERE  id = t_id(i);
    COMMIT;
END;
/

Using Bulk Collect : Populating two collections with 10,000 rows using a FOR..LOOP takes approximately 1.02 seconds:
DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
    t_id           id_type          := id_type();
  t_description  description_type := description_type();
    CURSOR c_data is SELECT * FROM   test1;
BEGIN
  FOR cur_rec IN c_data LOOP
    t_id.extend;
    t_description.extend;
    t_id(t_id.last)   :=  cur_rec.id;
    t_description(t_description.last) := cur_rec.description;
  END LOOP;
END;

Using the BULK COLLECT INTO construct reduces this time to approximately  0.01 seconds:
DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  t_id           id_type;
  t_description  description_type;
BEGIN
  SELECT id, description
  BULK COLLECT INTO t_id, t_description FROM test1;
END;

/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect