Today I got a problem on performance enhancement where the basic problem was to move data from one database (A) to another(B) with some transformation. B had exposed Stored Procs to insert and update data and A had to use this interface as per design. Now B only allow insert or update of 1 item at a time as it took parameters like varchar2, int and so on.This slowed down things a lot when bulk updates were required like 1000 rows at a time. So started looking google for something which can make things better.
I knew SQLLoader had parallel execution facilities but how to do it PL/SQL. The simplest was to pass a table of records to the SP (modified) and it will do the update one by one. Still it was technically sequential update with just saves on network transfer of one row at a time by making it a array of records. How to make it really parallel.
So found the Bulk Collect and ForALL of oracle. Never knew Oracle also implemented Collections like other object oriented languages. A simple self explainatory example is :
SQL> create or replace procedure fast_proc is
2 type TObjectTable is table of ALL_OBJECTS%ROWTYPE;
3 ObjectTable$ TObjectTable;
4 begin
5 select
6 * BULK COLLECT INTO ObjectTable$
7 from ALL_OBJECTS;
8
9 forall x in ObjectTable$.First..ObjectTable$.Last
10 insert into t1 values ObjectTable$(x) ;
11 end;
12 /
More details at :
while going through all this, I did learn the sql mantra:
I knew SQLLoader had parallel execution facilities but how to do it PL/SQL. The simplest was to pass a table of records to the SP (modified) and it will do the update one by one. Still it was technically sequential update with just saves on network transfer of one row at a time by making it a array of records. How to make it really parallel.
So found the Bulk Collect and ForALL of oracle. Never knew Oracle also implemented Collections like other object oriented languages. A simple self explainatory example is :
SQL> create or replace procedure fast_proc is
2 type TObjectTable is table of ALL_OBJECTS%ROWTYPE;
3 ObjectTable$ TObjectTable;
4 begin
5 select
6 * BULK COLLECT INTO ObjectTable$
7 from ALL_OBJECTS;
8
9 forall x in ObjectTable$.First..ObjectTable$.Last
10 insert into t1 values ObjectTable$(x) ;
11 end;
12 /
More details at :
- http://www.dba-oracle.com/oracle_tips_rittman_bulk%20binds_FORALL.htm
- http://www.akadia.com/services/ora_bulk_insert.html
- http://psoug.org/reference/array_processing.html
while going through all this, I did learn the sql mantra:
- You should do it in a single SQL statement if at all possible.
- If you cannot do it in a single SQL Statement, then do it in PL/SQL.
- If you cannot do it in PL/SQL, try a Java Stored Procedure.
- If you cannot do it in Java, do it in a C external procedure.
- If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…
