I was about to optimise some code from using FORALL to work set based, but a small test changed my mind.

I had some batch-processing code using object structures in PL/SQL. I had already written it, using the FORALL syntax to insert rows into a table. FORALL is pretty efficient, bulking up the values and reducing the context switches over to the SQL engine performing the inserts. Remembering the article I wrote last year about FORALL still being row-by-row processing, I thought I’d rewrite my code to use a set based approach to improve it even further. So – I wrote a small test to check out the expected improvement and I was surprised.

This was my test case:

-- Create a table to insert the data into
create table veitest
(
    id         number primary key,
    text1      varchar2(50),
    text2      varchar2(50),
    somenumber number
);
/

-- Create a simple object type to hold a collection in memory in PL/SQL:
create or replace type t_master as object
(
    id         number,
    text1      varchar2(50),
    text2      varchar2(50),
    somenumber number
);
/

create or replace type t_master_tab as table of t_master;
/

declare
    l_records        t_master_tab;
    l_start          timestamp;
    l_end            timestamp;

    procedure start_timer is
    begin
        l_start := systimestamp;
    end;

    procedure end_timer(p_label in varchar2) is
    begin
        l_end := systimestamp;
        dbms_output.put_line(l_end - l_start || ': ' || p_label);
    end;
begin
    rollback;
    -- Generate test-data and load it into memory
    start_timer;

    select * bulk collect
    into l_records
    from (select t_master(rownum,
                          lpad('x', 50, 'x'),
                          lpad('x', 50, 'x'),
                          rownum + 500)
          from dual
          connect by level <= 50000);

    end_timer('load');

    -- Write to the table using a FORALL-statement:
    start_timer;

    forall i in 1..l_records.count
        insert into veitest(id, text1, text2, somenumber)
        values (l_records(i).id, l_records(i).text1, l_records(i).text2, l_records(i).somenumber);

    end_timer('forall');
    rollback;

    -- Write to the table using a set-based SQL
    start_timer;
    insert into veitest(id, text1, text2, somenumber)
    select id, text1, text2, somenumber
    from table (l_records);
    end_timer('sql');

    rollback;
end;

-- With level <= 50.000:
+000000000 00:00:00.061386000: load
+000000000 00:00:00.034706000: forall
+000000000 00:00:00.058432000: sql

-- With level <= 500.000:
+000000000 00:00:00.423975000: load
+000000000 00:00:00.370070000: forall
+000000000 00:00:00.591293000: sql

So – the FORALL-statement ended up being faster. I expect the FORALL inserts to be fairly efficient, even though they show up as individual statement executions in the AWR report. The set-based version has probably some overhead in the conversion happening in the table (l_records) part.

So – even if the local DBA was worried about the millions of insert-executions showing up in the AWR-report, I was pleased, because the code in our batch was rather efficient.

And – of course – a regular for-loop would have been to slow.