Pl/SQL Bulk Bind/ Faster Update Statements

Question

I'm having problems using Bulk Bind in PL/SQL. Basically what I want is for a table(Component) to update its fieldvalue dependent on the Component_id and fieldname. All of these are passed in as paramaters (the type varchar2_nested_table is effectively and array of strings, one element for each update statement that needs to occur). So for instance if Component_id = 'Compid1' and fieldname = 'name' then fieldvalue should be updated to be 'new component name'.

I typed up the code below in relation to this http://www.oracle.com/technetwork/issue-archive/o14tech-plsql-l2-091157.html . The code works but is no faster than a simple loop that performs an update for every element in the IN parameters. So if the parameters have 1000 elements then 1000 update statements will be executed. I also realise I'm not using BULK COLLECT INTO but I didn't think I needed it as I don't need to select anything from the database, just update.

At the moment both take 4-5 seconds for 1000 updates. I assume I'm using the bulk bind incorrectly or have a misunderstanding of the subject as in examples I can find people are executing 50,000 rows in 2 seconds etc. From what I understand FORALL should improve performance by reducing the number of context switches. I have tried another method I found online using cursors and bulk binds but had the same outcome. Perhaps my performance expectations are too much? I don't think so from seeing others results. Any help would be greatly appreciated.

create or replace procedure BulkUpdate(sendSubject_in IN varchar2_nested_table_type,
fieldname_in IN varchar2_nested_table_type,fieldvalue_in IN   varchar2_nested_table_type) is


TYPE component_aat IS TABLE OF component.component_id%TYPE
  INDEX BY PLS_INTEGER;
TYPE fieldname_aat IS TABLE OF component.fieldname%TYPE
  INDEX BY PLS_INTEGER;
TYPE fieldvalue_aat IS TABLE OF component.fieldvalue%TYPE
  INDEX BY PLS_INTEGER;

fieldnames fieldname_aat;
fieldvalues fieldvalue_aat;
approved_components component_aat;


PROCEDURE partition_eligibility
IS
BEGIN
  FOR indx IN sendSubject_in.FIRST .. sendSubject_in.LAST
  LOOP
    approved_components(indx) := sendSubject_in(indx);
    fieldnames(indx):= fieldname_in(indx);
    fieldvalues(indx) := fieldvalue_in(indx);
  END LOOP;
END;


PROCEDURE update_components
IS
BEGIN
  FORALL indx IN approved_components.FIRST .. approved_components.LAST
    UPDATE Component
      SET Fieldvalue = fieldvalues(indx)
      WHERE Component_id = approved_components(indx)
      AND Fieldname = fieldnames(indx);
END;

BEGIN
  partition_eligibility;
  update_components;
END BulkUpdate;

Answer 1

There is something else going on, I suspect your individual updates are each taking a lot of time, maybe because there are triggers or inefficient indexes. (Note that if each statement is expensive individually, using bulk updates won't save you a lot of time since the context switches are negligible compared to the actual work).

Here is my test setup:

CREATE TABLE Component (
  Component_id NUMBER,
  fieldname    VARCHAR2(100),
  Fieldvalue   VARCHAR2(100),
  CONSTRAINT component_pk PRIMARY KEY (component_id, fieldname)
);

-- insert 1 million rows
INSERT INTO component 
  (SELECT ROWNUM, to_char(MOD(ROWNUM, 100)), dbms_random.string('p', 10) 
     FROM dual 
   CONNECT BY LEVEL <= 1e6);

CREATE OR REPLACE TYPE varchar2_nested_table_type AS TABLE OF VARCHAR2(100);
/

SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
DECLARE
   l_id    varchar2_nested_table_type;
   l_names varchar2_nested_table_type;
   l_value varchar2_nested_table_type;
   l_time  NUMBER;
BEGIN
   SELECT rownum, to_char(MOD(rownum, 100)), dbms_random.STRING('p', 10) 
     BULK COLLECT INTO l_id, l_names, l_value
     FROM dual
   CONNECT BY LEVEL <= 100000;
   l_time := dbms_utility.get_time;
   BulkUpdate(l_id, l_names, l_value);
   dbms_output.put_line((dbms_utility.get_time - l_time) || ' cs elapsed.');
END;
/

100000 rows updated in about 1.5 seconds on an unremarkable test machine. Updating the same data set row by row takes about 4 seconds.

Can you run a similar script with a newly created table?