Swell example of just how bulk collects, collections and bulk insert/updates ought to interact. For maintainability, and since I'm a lazy typist, I'd make a couple of changes to the code pro-forma.

In lieu of your record of dbms_sql.*_table's I'd use a PL/SQL table (or perhaps varray?) but most importantly declare it based on the structure of the cursor for ease of maintenance down the road:

5 type empRec is record
6 (
7 empno dbms_sql.number_table,
8 ename dbms_sql.varchar2_table,
9 job dbms_sql.varchar2_table,
10 mgr dbms_sql.number_table,
11 hiredate dbms_sql.date_table,
12 sal dbms_sql.number_table,
13 comm dbms_sql.number_table,
14 deptno dbms_sql.number_table,
15 rowid rowidArray,
16 changed dbms_sql.varchar2_table
17 );

becomes:

TYPE empTab IS TABLE OF c%ROWTYPE;
l_records empTab;

And then change the fetch:

fetch c bulk collect into l_record.empno, l_record.ename, l_record.job,
46 l_record.mgr, l_record.hiredate,
47 l_record.sal, l_record.comm, l_record.deptno,
48 l_record.rowid, l_record.changed

to

FETCH c BULK COLLECT INTO l_records
...

cheers,
Scott

Oh, that you could.... but you cannot....

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          type empTab is table of emp%rowtype index by binary_integer;
  3          l_records empTab;
  4          cursor c is select * from emp;
  5  begin
  6          open c;
  7          fetch c bulk collect into l_records;
  8          close c;
  9  end;
 10  /
        fetch c bulk collect into l_records;
                                  *
ERROR at line 7:
ORA-06550: line 7, column 28:
PLS-00597: expression 'L_RECORDS' in the INTO list is of wrong type
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored


ops$tkyte@ORA817DEV.US.ORACLE.COM> declare<b>
  2          type empTab is table of number index by binary_integer;</b>
  3          l_records empTab;
  4          cursor c is select<b> empno</b> from emp;
  5  begin
  6          open c;
  7          fetch c bulk collect into l_records;
  8          close c;
  9  end;
 10  /

PL/SQL procedure successfully completed.


You cannot bulk collect into an ARRAY OF RECORDS.  You can into a RECORD OF ARRAYS..... 

Tom,

I plan on sharing this with all of our developers.

Thanks!

Hi Tom,

Thanks for this explanation.I've tried several times to manage this but couldn't.
Could you please just explain
dbms_sql.number_table .I presume it is declaration of pl/sql table in record type?

I was always fascinated by the bulk methods but never used
it because I never saw an example of FOR ALL clause with
END LOOP. The example I have seen show only one line
command after FOR ALL so its good to know that FOR ALL
can handle a multi line transaction with END LOOP.

My question is related to the same in this thread
where user wants to collect in bulk and process the data.

Why are using an extra step to process the array of
records and then updating the data in next step.
Why can't we do the processing in FOR ALL block.

Thanks,

Never knew one could do this using Oracle.
Just great !

Hi Tom

I have a procedure which takes 10 hours to process 1 million rows, I am not sure if it can be optimized with forall array processing.
The code is something like

DECLARE
CURSOR c1
IS SELECT cust_id FROM x_temp;
l_custid NUMBER;
l_varob NUMBER;
l_cnt NUMBER := 0;
l_objid NUMBER;
BEGIN
for i in c1
loop
begin
l_cnt := l_cnt + 1;
select blah blah
into l_custid
from x_cust
where cust_id = c1.cust_id;
update x_cust blah blah;
exception
when
no_data_found
then
begin
select obj_id
into l_objid
from x_obj
where blah blah;
update x_cust;
exception
when no_data_found then blah blah;
end;
when others then update x_temp;
end;
if l_cnt = 5000 then commit; l_cnt := 0; end if;
end loop;
END;
/

I have to process row by row and there are 1 million rows. Can this kind of process use array processing? Or conventional cursor is the only bet?

!!!

Hi Tom,

I just replicated your do_it procedure in this way

1 DECLARE
2 TYPE EMPREC IS RECORD (P_EMPNO T_EMP.EMPNO%TYPE,
3 P_EMPNM T_EMP.EMPNM%TYPE,
4 P_DEPT T_EMP.DEPT%TYPE,
5 P_SALARY T_EMP.SALARY%TYPE,
6 P_COMM T_EMP.COMM%TYPE);
7 VREC EMPREC;
8 CURSOR C1 IS SELECT EMPNO,EMPNM,DEPT,SALARY,COMM FROM T_EMP;
9 BEGIN
10 OPEN C1;
11 LOOP
12 FETCH C1 BULK COLLECT INTO VREC.P_EMPNO,VREC.P_EMPNM,VREC.P_DEPT,VREC.P_SALARY,VREC.P_COMM;
13 EXIT WHEN C1%NOTFOUND;
14 END LOOP;
15 DBMS_OUTPUT.PUT_LINE('STATUS IS OK');
16* END;
17 /
FETCH C1 BULK COLLECT INTO VREC.P_EMPNO,VREC.P_EMPNM,VREC.P_DEPT,VREC.P_SALARY,VREC.P_COMM;
*
ERROR at line 12:
ORA-06550: line 12, column 4:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
ORA-06550: line 12, column 4:
PL/SQL: SQL Statement ignored

. Why this gave such error and in your case not?

Tom,

If you could, can you highlight the differences between an "record of arrays" and "array of records" with some simple examples. And when one might use what?

Hi Tom,

Is it possible to bulk append to a collection. Say I have a collection x that already has 5 elements populated. Now I want to add 10 elements to it. Is there a way to avoid, in the case of nested table, doing
loop
x.extend;
x(i):=something;
end loop;

Obviouly if I do a select BULK COLLECTION INTO x again, all the 5 existing elements will be over written.

- John

Hi Tom,
I just overlooked your above emprec definition. Thanks a lot for the clarification.

Hi Tom,
I just overlooked the emprec definition. Thanx for the clarification.

tom,

This is my question.

Table A
ID -- Primary key
col1
col2
col3
col4
col5

Now Session 1:
select 1 into var1 where id = 1 for update;
-- can we say select 1 into var2 where id = 1 for update of col1, col2 ? And does this resolve the lock issue?
update a set col = xxx, col2=yyyy where id = 1;

Session 2:
select 1 into var1 where id = 1 for update;
update a set col4=zzzz, col5=qqqq where id = 1;

Now, when these are executed simultaneouly, though on the same row for DIFFERENT COLUMNS, how to resolve this locking issue. We have these update statements in our batch jobs that get kicked off every night at same time. The values for the columns come from different sources.

Your help would be much appreciated. thanks much.

Thanks Tom. I thought so, but just want to confirm. thanks again.

<quote>
totally different data structures. 9i permits bulk collection into arrays of
records, 8i did not.
</quote>

Could you please give a small example?
I tried this in 9iR2:

create table rec as select object_id id, object_name name from all_objects where rownum<=100

declare
type record_t is record
(
id number,
name varchar2(30)
);

type arr_t is table of record_t;
arr record_t;
begin
select * bulk collect into arr from rec;
for i in 1..100 loop
dbms_output.put_line('id=' || arr(i).id || ' name=' || arr(i).name);
end loop;
end;

The following error has occurred:

ORA-06550: line 11, column 3:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
ORA-06550: line 11, column 34:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 11, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 16:
PLS-00222: no function with name 'ARR' exists in this scope
ORA-06550: line 13, column 5:
PL/SQL: Statement ignored

Cheers

ops$tkyte@ORA920LAP> create table rec as select object_id id, object_name name from all_objects where rownum<=100
  2  /

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> declare
  2  type record_t is record
  3  (
  4  id number,
  5  name varchar2(30)
  6  );
  7
  8  type arr_t is table of record_t;
  9  arr <b>ARR_T;</b>
 10  begin
 11       select * bulk collect into arr from rec;
 12       for i in 1..100 loop
 13            dbms_output.put_line('id=' || arr(i).id || ' name=' || arr(i).name);
 14       end loop;
 15  end;
 16  /
id=17287 name=/1005bd30_LnkdConstant
id=7559 name=/10076b23_OraCustomDatumClosur
....


your code was trying to bulk collect into a RECORD, not an array of records ;) 

Sorry...

It works! It was my typo:

should be:
arr arr_t;

instead of:
arr record_t;

Regards

Excellent

Dear Tom,

One of my daily night process takes around 12 hours to completes which processes around 6 Lakhs of rows. The logic built was that the number of rows to be processed are inserted into a table which is first truncated. This is a simple insert (insert into <tablename> select * from <another_tablename>. The logic built by the application tea was

declare
define a cursor
begin
for cursor
loop
processing.... (calling some procedures / functions)
inserting into another table
end loop;

It processes 6 lakhs records. I wanted to use bulk collect feature using records as shown by you in the top. But when compared to single processing the tieme taken is same in both the cases. i.e single record processing takes same amount of time as buld processing. Based on the logic developed by our developer, I have created a package with both the options (Bulk Fetch and Single Fetch).
The package is as under :


create or replace package vivek
as
procedure do_it;
procedure do_it_single;
function check_odd_even(objid in number) return varchar2;
function check_prime(objid in number) return varchar2;
end;
/

create or replace package body vivek
as
type rowidArray is table of rowid index by binary_integer;

type vivekRec is record
(
t_owner dbms_sql.varchar2_table,
t_object_name dbms_sql.varchar2_table,
t_object_id dbms_sql.number_table,
t_odd_even dbms_sql.varchar2_table,
t_prime dbms_sql.varchar2_table,
t_rowid rowidArray
);

function check_prime(objid in number)
return varchar2
is
l_value number default 0;
l_cnt number default 2;
Begin
while l_cnt<objid
loop
l_value:=mod(objid,l_cnt);
if l_value=0 then
l_cnt:=objid;
end if;
l_cnt:=l_cnt+1;
end loop;
if l_value = 0 then
return 'Not a Prime No.';
else
return 'Prime No.';
end if;
end;
-- end of prime function


function check_odd_even(objid in number) return varchar2
is
l_value number;
begin
l_value:=mod(objid,2);
if l_value = 0 then
return 'Even';
else
return 'Odd';
end if;
end;
---------------- end of odd_even function

procedure do_it is
l_record vivekRec;
l_array_size number default 1000;
l_cnt number default 1;
l_start number;
l_run number;
cursor main is
select rowid, owner, object_name, object_id
from vivek_test
where rownum<=20000;
begin
-- execute immediate ('truncate table vivek_completion_time');
update vivek_completion_time
set time_taken=null
where processing_for='Bulk Processing';
l_start:=dbms_utility.get_time;
open main;
loop
dbms_application_info.set_client_info
('processing '||l_cnt||' thru '||(l_cnt+l_array_size-1));

fetch main bulk collect into l_record.t_rowid, l_record.t_owner, l_record.t_object_name,
l_record.t_object_id limit l_array_size;


for i in 1..l_record.t_owner.count
loop
-- to check whether the object_id is prime number;
l_record.t_prime(i):= check_prime(l_record.t_object_id(i));
-- check whether the object_id is odd number
l_record.t_odd_even(i) := check_odd_even(l_record.t_object_id(i));
end loop;


forall i in 1..l_record.t_owner.count

-- if l_record.t_prime='Prime No.' and l_record.t_odd_even='Odd' then
insert into vivek_test2_prime(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
select l_record.t_owner(i), l_record.t_object_name(i), l_record.t_prime(i),
l_record.t_object_id(i), sysdate, sysdate, l_record.t_odd_even(i)
from vivek_test
where rowid = l_record.t_rowid(i)
and l_record.t_prime(i)='Prime No.';
-- and l_record.t_odd_even(i)='Odd';

forall i in 1..l_record.t_owner.count
insert into vivek_test2_odd(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
select l_record.t_owner(i), l_record.t_object_name(i), l_record.t_prime(i),
l_record.t_object_id(i), sysdate, sysdate, l_record.t_odd_even(i)
from vivek_test
where rowid = l_record.t_rowid(i)
and l_record.t_odd_even(i)='Odd';

-- elsif l_record.t_prime='Prime No.' and l_record.t_odd_even='Even' then

forall i in 1..l_record.t_owner.count
insert into vivek_test2_even(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
select l_record.t_owner(i), l_record.t_object_name(i), l_record.t_prime(i),
l_record.t_object_id(i), sysdate, sysdate, l_record.t_odd_even(i)
from vivek_test
where rowid = l_record.t_rowid(i)
and l_record.t_odd_even(i)='Even';

-- elsif l_record.t_prime='Not a Prime No.' and l_record.t_odd_even='Even' then
forall i in 1..l_record.t_owner.count
insert into vivek_test2_prime(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
select l_record.t_owner(i), l_record.t_object_name(i), l_record.t_prime(i),
l_record.t_object_id(i), sysdate, sysdate, l_record.t_odd_even(i)
from vivek_test
where rowid = l_record.t_rowid(i)
and l_record.t_prime(i)='Not a Prime No.';
-- and l_record.t_odd_even(i)='Even';

forall i in 1..l_record.t_owner.count
insert into vivek_test2(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values (l_record.t_owner(i), l_record.t_object_name(i), l_record.t_prime(i),
l_record.t_object_id(i), sysdate, sysdate, l_record.t_odd_even(i));
exit when main%notfound;
l_cnt:=l_cnt+l_array_size;

end loop;
close main;
commit;
l_run:=dbms_utility.get_time-l_start;
update vivek_completion_time
set time_taken=ltrim(rtrim(to_char(l_run)))||' hsecs.'
where processing_for='Bulk Processing';
-- insert into vivek_completion_time values(l_run||' hsecs.');
-- dbms_output.put_line('Time Taken '||l_run||' secs.');
commit;
end;
-- end of do_it procedure


procedure do_it_single is
t_owner vivek_test.owner%type;
t_object_name vivek_test.object_name%type;
t_object_id vivek_test.object_id%type;
l_cnt number default 1;
l_start number;
l_run number;
t_prime varchar2(30);
t_odd_even varchar2(30);
l_array_size number:=20000;

cursor main is
select owner, object_name, object_id
from vivek_test
where rownum<=l_array_size;
begin

update vivek_completion_time
set time_taken=null
where processing_for='Single Processing';
l_start:=dbms_utility.get_time;
open main;
loop
dbms_application_info.set_client_info
('processing '||l_cnt||' thru '||(l_array_size));
fetch main into t_owner, t_object_name,
t_object_id;
t_prime:= check_prime(t_object_id);
t_odd_even := check_odd_even(t_object_id);

if t_prime='Prime No.' and t_odd_even='Odd' then
insert into vivek_test2_prime(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values(t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);

insert into vivek_test2_odd(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values (t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);
elsif t_prime='Prime No.' and t_odd_even='Even' then

insert into vivek_test2_prime(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values(t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);
insert into vivek_test2_even(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values (t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);

elsif t_prime='Not a Prime No.' and t_odd_even='Odd' then
-- and l_record.t_odd_even='Even' then
insert into vivek_test2_prime(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values(t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);
insert into vivek_test2_odd(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values (t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);
else
insert into vivek_test2_prime(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values(t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);
insert into vivek_test2_even(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values (t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);
end if;
insert into vivek_test2(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
values (t_owner, t_object_name, t_prime,
t_object_id, sysdate, sysdate, t_odd_even);
exit when main%notfound;
l_cnt:=l_cnt+1;
end loop;
close main;
commit;
l_run:=dbms_utility.get_time-l_start;
update vivek_completion_time
set time_taken=ltrim(rtrim(to_char(l_run)))||' hsecs.'
where processing_for='Single Processing';
-- insert into vivek_completion_time values(l_run||' hsecs.');
-- dbms_output.put_line('Time Taken '||l_run||' secs.');
commit;

end;
-- end of do_it_single procedure

end; -- end of package
/

The procedure do_it uses bulk processing with 1000 rows as limit and do_it_single processes single row at a time. the time taken by both the processes is inserted into another table after completion of this process. The check was made for 20000 rows.

TIME_TAKEN PROCESSING_FOR
-------------------- --------------------
50973 hsecs. Bulk Processing
51774 hsecs. Single Processing

In do_it procedure, everytime the object_id is checked for prime number check and odd_even check. Then it gets inserted using forall. There are no indexes on the main table which is vivek_test.

What I assume is that since I am calling the functions in a loop using for loop and this loop is being executed total no. of records time hence there can be no improvement. Is there any way that I can pass bulk parameters to functions instead of running the loop 20000 times. How do I improve the performance of do_it procedure over do_it_single procedure. I have also tried to skip the for loop and modify all the forall's as
insert into vivek_test2_odd(owner, object_name, subobject_name, object_id,
created, last_ddl_time, status)
select l_record.t_owner(i), l_record.t_object_name(i), check_prime(l_record.t_object_id(i)),
l_record.t_object_id(i), sysdate, sysdate, check_odd_even(l_record.t_object_id(i))
from vivek_test
where rowid = l_record.t_rowid(i)
and check_odd_even(l_record.t_object_id(i))='Odd';

But it took more time than the previous.
Please help. This will help me enhance my knowledge towards application tuning. There are 3-4 similar processes which I need to improve in terms of response time.

Regards
Vivek

Hi Tom,

You said that in 8i it would not be possible to bulk collect in arrays of records. I have a similar question for bulk binds. Can we bulk bind arrays of records or arrays of some other data type using the forall statement in 8i. In case it is not possible then can we bulk bind arrays of records/"some data type" using subscripting to bind the element seperately?

Kapil

Thanks for an excellent explanation of the bulk collect and forall. I was able to quickly take your example and apply it to my case. Thanks for helping me keep my job!! =)

Hello Tom,
I am making exact the same as you but it dosen't work. Why?
SQL> DECLARE
  2    TYPE Service_T IS RECORD
  3    (
  4      id           NUMBER
  5     ,service_name VARCHAR2(250)
  6     ,description  VARCHAR2(100)
  7    );
...
 10    service_list   Service_T ;
...
157    service_list.EXTEND;
158    service.id           := 1;
159    service.service_name := 'XXX';
160    service.description  := 'YYY';
...
175    FORALL i IN 1 .. service_list.id.count
176      INSERT INTO onmanage.lang_services (id
177                                         ,description
178                                         ,service_name)
179      VALUES (service_list.id.(i)
180             ,service_list.description(i)
181             ,service_list.service_name(i))
182      RETURNING id BULK COLLECT INTO serviceid_list;
...
...
ERROR at line 179:
ORA-06550: line 179, column 29:
PL/SQL: ORA-01747: invalid user.table.column, table.column, or column
specification

Thanks
Shahrouz DARVISHZADEH 

Sorry I didn't want to waste your time. Here a "working" fragment of the code. I know how it looks like. Database is getting data from a sensor. Therefore this funny "1 row insertion".

Thank you very much
Shahrouz Darvishzadeh

SQL> DECLARE
  2    TYPE Service_T IS RECORD
  3    (
  4      id           NUMBER
  5     ,service_name VARCHAR2(250)
  6     ,description  VARCHAR2(100)
  7    );
  8    TYPE Service_LT   IS TABLE OF Service_T;
  9    service_list   Service_T ; --  := Service_LT();
       serviceid_list ServiceId_LT;
 10  BEGIN
 11    -- ============
 12    -- Prepare data
 13    -- ============
 14    service_list.EXTEND;
 15    service.id           := 1002000001;
 16    service.service_name := 'XXX';
 17    service.description  := 'YYY';
 18
 19    -- ===================
 20    -- Insert service data
 21    -- ===================
 22    FORALL i IN 1 .. service_list.id.count
 23      INSERT INTO onmanage.lang_services (id
 24                                         ,description
 25                                         ,service_name)
 26      VALUES (service_list.id.(i)
 27             ,service_list.description(i)
 28             ,service_list.service_name(i))
 29      RETURNING id BULK COLLECT INTO serviceid_list;
 30  END;
 31  /
    VALUES (service_list.id.(i)
                            *
ERROR at line 26:
ORA-06550: line 26, column 29:
PL/SQL: ORA-01747: invalid user.table.column, table.column, or column
specification
ORA-06550: line 23, column 5:
PL/SQL: SQL Statement ignored 

I was very interested in the question posed by "A Reader" (the one where you replied,
"better idea.

lose the commit.
lose the procedure"

Sadly (for A Reader and me), you asked for the question to be asked as a new one. Fair enough, but now I'm wondering if A Reader ever did ask the question, again.

Frustrated, an idea popped into my head: why not have user IDs (a bit like OTN IDs), so that it'd be easy to search for questions asked by certain persons? Also, it'd be nice to be able to sort the "Recent" questions by date, age, and the question-poser's ID.

Hi Tom,

After reading this thread, could you please confirm that this is the way to declare types/variables for BULK COLLECT with FORALL UPDATE:

CURSOR c_extract IS
SELECT col1, col2 ....;
TYPE rt_extract IS RECORD( col1 dbms_sql.Varchar2_Table,
col2 dbms_sql.Varchar2_Table...);
l_extract rt_extract;
BEGIN
OPEN c_extract;
LOOP
FETCH c_extract BULK COLLECT INTO l_extract.col1,
l_extract_col2;
---
FORALL i IN 1..l_extract.col1.COUNT
UPDATE a_table SET a.col3 = l_extract.col1(i) ....;
---
I will be using l_extract a few more times to update other tables.
EXIT WHEN c_extract%NOTFOUND
END LOOP;
END;

Also, would you have any suggestions as how I could make this prettier please?

FORALL i IN 1..l_extract_rt.sl_rowid.COUNT SAVE EXCEPTIONS
INSERT INTO payment_instructions (
account_id
, sub_account_id
, min_amount)
SELECT sa.account_id
, sa.sub_account_id
, TO_NUMBER(l_extract_rt.sl_new_repayment_amt(i))
FROM sub_accounts sa
WHERE NOT EXISTS (SELECT NULL
FROM payment_instructions pi2
, sub_accounts sa2
WHERE pi2.ac_transfer_type_id = lc_msapd
AND pi2.next_payment_date = TO_DATE(l_extract_rt.sl_effective_date(i), lc_dt_format))
AND pi2.account_id = sa2.account_id
AND pi2.sub_account_id = sa2.sub_account_id
AND sa2.sub_account_number = l_extract_rt.sl_bn_if_accno(i)
AND sa.sub_account_number = l_extract_rt.sl_bn_if_accno(i)
AND l_extract_rt.sl_first_payment_indicator(i) = lc_first_pymnt
UNION ALL
SELECT sa.account_id
, sa.sub_account_id
, TO_NUMBER(l_extract_rt.sl_new_repayment_amt(i))
FROM sub_accounts sa
, payment_instructions pi
WHERE NOT EXISTS (SELECT NULL
FROM payment_instructions pi2
, sub_accounts sa2
WHERE pi2.next_payment_date = TO_DATE(l_extract_rt.sl_effective_date(i), lc_dt_format)
AND pi2.ac_transfer_type_id = lc_msapd
AND pi2.account_id = sa2.account_id
AND pi2.sub_account_id = sa2.sub_account_id
AND sa2.sub_account_number = l_extract_rt.sl_bn_if_accno(i))
AND pi.next_payment_date = (SELECT MAX(pi1.next_payment_date)
FROM payment_instructions pi1
WHERE pi1.next_payment_date < TO_DATE(l_extract_rt.sl_effective_date(i),lc_dt_format)
AND pi1.ac_transfer_type_id = lc_msapd
AND pi1.sub_account_id = sa.sub_account_id
AND pi1.account_id = sa.account_id)
AND pi.ac_transfer_type_id = lc_msapd
AND sa.account_id = pi.account_id
AND sa.sub_account_id = pi.sub_account_id
AND sa.sub_account_number = l_extract_rt.sl_bn_if_accno(i)

ops$tkyte@ORA9IR2> create table t as select * from all_users where 1=0;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          type array is table of t%rowtype;
  3          l_data array;
  4          cursor c is select * from all_users;
  5  begin
  6          open c;
  7          loop
  8                  fetch c bulk collect into l_data limit 10;
  9                  forall i in 1 .. l_data.count
 10                          insert into t values l_data(i);
 11                  exit when c%notfound;
 12          end loop;
 13          close c;
 14  end;
 15  /

PL/SQL procedure successfully completed.



is another way...


to make that prettier - sure, it would look like this:


-- The logic of the following SELECT is as follows 
-- afdsa fad af daf ad fa fa dfa
-- ............ nice comment explaining what this does 
-- how it does it, and so on...
--

FORALL i IN 1..l_extract_rt.sl_rowid.COUNT SAVE EXCEPTIONS
INSERT INTO payment_instructions (
                      account_id
                    , sub_account_id
                    , min_amount)
....... code as is, looks pretty to me 

Using the definition you suggested is perfect for FORALL inserts. However I wish to perform updates. In that case I would need to do as follows:

declare
type array is table of t%rowtype;
l_data array;
type t_username is table of t.username%TYPE INDEX BY PLS_INTEGER;
l_username t_username;
type t_created is table of t.created%TYPE INDEX BY PLS_INTEGER;
l_created t_created;

cursor c is select * from all_users;
begin
open c;
loop
fetch c bulk collect into l_data limit 10;
for i in 1 .. l_data.count loop
l_username(i) := l_data(i).username;
l_created(i) := l_data(i).created + 1;
end loop;
forall i in 1 .. l_data.count
insert into t values l_data(i);
forall i in 1 .. l_data.count
update t set userid = 'NN', created = l_created(i) where username = l_username(i);
exit when c%notfound;
end loop;
close c;
end;
/

Hi Tom,

building on my previous question please, how would you go about resolving the issue where either an update or insert is required depending on an indicator.
I have made use of the bulk collect, and then I would like to use forall update/ forall insert. However I know that a forall can only accept one dml statement. I can see in another thread that you offered a way of forall inserting into different tables.
Perhaps the best way would be to use a MERGE statement? PS! I cannot do a forall insert and then a forall update since the records must be processed in timestamp order.
Any hints would be very much appreciated thanks.

I can see you have already answered this question somewhere else: </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8912264456901 <code>

It explains it all. Thanks!

Tom,

Following is our ETL logic. We take one record from a source and then based on some criteria split that record into almost 24 records, and then decide either to update those 24 records or to insert them in the target table. If they already exist then update , and if they don't exist then insert them. 

The source records are around 26000. And after processing them they become 26000 * 24. 
And taking a lot of time.
Can we take some benefit of bulk loading in the following particular scenerio to improve performance. OR anything else that can give us a performance shoot.


SQL> CREATE TABLE EMP_ETL (EMPNO NUMBER, ETL_VALUE VARCHAR2(10), ETL_CRITERIA NUMBER);

Table created.

Elapsed: 00:00:00.00

SQL>  DECLARE
  2      CURSOR C1 IS
  3      SELECT EMPNO,JOB,SAL,DEPTNO FROM EMP;
  4      L_EMPNO NUMBER;
  5  BEGIN
  6      FOR REC IN C1 LOOP
  7         BEGIN
  8           SELECT    A.EMPNO
  9               INTO      L_EMPNO
 10           FROM      EMP_ETL A,
 11                         EMP_ETL B,
 12                         EMP_ETL C
 13               WHERE     A.EMPNO = B.EMPNO
 14               AND       A.EMPNO = C.EMPNO
 15               AND       B.EMPNO = C.EMPNO
 16               AND       A.ETL_CRITERIA = 1
 17               AND       B.ETL_CRITERIA = 2
 18               AND       C.ETL_CRITERIA = 3
 19            AND          A.EMPNO = REC.EMPNO;
 20            UPDATE EMP_ETL
 21                 SET     ETL_VALUE     = REC.JOB
 22                 WHERE     ETL_CRITERIA= 1
 23                    AND         EMPNO = REC.EMPNO;
 24                  UPDATE EMP_ETL
 25                 SET ETL_VALUE = REC.SAL
 26                 WHERE ETL_CRITERIA = 2
 27                    AND   EMPNO = REC.EMPNO;
 28                  UPDATE EMP_ETL
 29                 SET ETL_VALUE = REC.DEPTNO
 30                 WHERE ETL_CRITERIA = 3
 31                    AND EMPNO = REC.EMPNO;
 32         EXCEPTION
 33             WHEN NO_DATA_FOUND THEN
 34                     INSERT INTO EMP_ETL
 35                             VALUES (REC.EMPNO,REC.JOB,1);
 36                     INSERT INTO EMP_ETL
 37                             VALUES (REC.EMPNO,REC.SAL,2);
 38                     INSERT INTO EMP_ETL
 39                             VALUES (REC.EMPNO,REC.DEPTNO,3);
 40         END;
 41       END LOOP;
 42* END;
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> SELECT * FROM EMP_ETL;

     EMPNO ETL_VALUE  ETL_CRITERIA                                              
---------- ---------- ------------                                              
      7369 CLERK                 1                                              
      7369 800                   2                                              
      7369 20                    3                                              
      7499 SALESMAN              1                                              
      7499 1600                  2                                              
      7499 30                    3                                              
      7521 SALESMAN              1                                              
      7521 1250                  2                                              
      7521 30                    3                                              
      7566 MANAGER               1                                              
      7566 2975                  2                                              
      7566 20                    3                                              
      7654 SALESMAN              1                                              
      7654 1250                  2                                              
      7654 30                    3                                              
      7698 MANAGER               1                                              
      7698 2850                  2                                              
      7698 30                    3                                              
      7782 MANAGER               1                                              
      7782 2450                  2                                              
      7782 10                    3                                              
      7788 ANALYST               1                                              
      7788 3000                  2                                              
      7788 20                    3                                              
      7839 PRESIDENT             1                                              
      7839 5000                  2                                              
      7839 10                    3                                              
      7844 SALESMAN              1                                              
      7844 1500                  2                                              
      7844 30                    3                                              
      7876 CLERK                 1                                              
      7876 1100                  2                                              
      7876 20                    3                                              
      7900 CLERK                 1                                              
      7900 950                   2                                              
      7900 30                    3                                              
      7902 ANALYST               1                                              
      7902 3000                  2                                              
      7902 20                    3                                              
      7934 CLERK                 1                                              
      7934 1300                  2                                              
      7934 10                    3                                              

42 rows selected.

Elapsed: 00:00:00.03
SQL>  DECLARE
  2      CURSOR C1 IS
  3      SELECT EMPNO,JOB,SAL,DEPTNO FROM EMP;
  4      L_EMPNO NUMBER;
  5  BEGIN
  6      FOR REC IN C1 LOOP
  7         BEGIN
  8           SELECT    A.EMPNO
  9               INTO      L_EMPNO
 10           FROM      EMP_ETL A,
 11                         EMP_ETL B,
 12                         EMP_ETL C
 13               WHERE     A.EMPNO = B.EMPNO
 14               AND       A.EMPNO = C.EMPNO
 15               AND       B.EMPNO = C.EMPNO
 16               AND       A.ETL_CRITERIA = 1
 17               AND       B.ETL_CRITERIA = 2
 18               AND       C.ETL_CRITERIA = 3
 19            AND          A.EMPNO = REC.EMPNO;
 20            UPDATE EMP_ETL
 21                 SET     ETL_VALUE     = REC.JOB
 22                 WHERE     ETL_CRITERIA= 1
 23                    AND         EMPNO = REC.EMPNO;
 24                  UPDATE EMP_ETL
 25                 SET ETL_VALUE = REC.SAL
 26                 WHERE ETL_CRITERIA = 2
 27                    AND   EMPNO = REC.EMPNO;
 28                  UPDATE EMP_ETL
 29                 SET ETL_VALUE = REC.DEPTNO
 30                 WHERE ETL_CRITERIA = 3
 31                    AND EMPNO = REC.EMPNO;
 32         EXCEPTION
 33             WHEN NO_DATA_FOUND THEN
 34                     INSERT INTO EMP_ETL
 35                             VALUES (REC.EMPNO,REC.JOB,1);
 36                     INSERT INTO EMP_ETL
 37                             VALUES (REC.EMPNO,REC.SAL,2);
 38                     INSERT INTO EMP_ETL
 39                             VALUES (REC.EMPNO,REC.DEPTNO,3);
 40         END;
 41       END LOOP;
 42 END;
 43  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> SELECT * FROM EMP_ETL;

     EMPNO ETL_VALUE  ETL_CRITERIA                                              
---------- ---------- ------------                                              
      7369 CLERK                 1                                              
      7369 800                   2                                              
      7369 20                    3                                              
      7499 SALESMAN              1                                              
      7499 1600                  2                                              
      7499 30                    3                                              
      7521 SALESMAN              1                                              
      7521 1250                  2                                              
      7521 30                    3                                              
      7566 MANAGER               1                                              
      7566 2975                  2                                              
      7566 20                    3                                              
      7654 SALESMAN              1                                              
      7654 1250                  2                                              
      7654 30                    3                                              
      7698 MANAGER               1                                              
      7698 2850                  2                                              
      7698 30                    3                                              
      7782 MANAGER               1                                              
      7782 2450                  2                                              
      7782 10                    3                                              
      7788 ANALYST               1                                              
      7788 3000                  2                                              
      7788 20                    3                                              
      7839 PRESIDENT             1                                              
      7839 5000                  2                                              
      7839 10                    3                                              
      7844 SALESMAN              1                                              
      7844 1500                  2                                              
      7844 30                    3                                              
      7876 CLERK                 1                                              
      7876 1100                  2                                              
      7876 20                    3                                              
      7900 CLERK                 1                                              
      7900 950                   2                                              
      7900 30                    3                                              
      7902 ANALYST               1                                              
      7902 3000                  2                                              
      7902 20                    3                                              
      7934 CLERK                 1                                              
      7934 1300                  2                                              
      7934 10                    3                                              

42 rows selected.

Elapsed: 00:00:00.03 

Thanks Tom for the useful information.
I was wondering is there any way we can use like this

FORALL i IN 1 .. t_id.COUNT

UPDATE emp
SET sal=NULL
WHERE emp_id=t_id(i);

DELETE FROM dept
WHERE emp_id=t_id(i);

Currently I kept two times
FORALL i IN 1 .. t_id.COUNT

Hope this is the correct way.
FORALL i IN 1 .. t_id.COUNT
UPDATE emp
SET sal=NULL
WHERE emp_id=t_id(i);


FORALL i IN 1 .. t_id.COUNT
DELETE FROM dept
WHERE emp_id=t_id(i);



Regards,
Ammu

CREATE OR REPLACE procedure proc(p_trade_dt in number)
is
cursor cur_a is
select val
,tradedate
,trade_dt_key
,(price1+price2) price
,((price1+price2)/2 mid_price
,decode( typ, 'B', QTY, 0 ) buy
,decode( typ, 'S', QTY, 0 ) sell
,(decode( type, 'B', QTY, 0 ) + decode( typ, 'S', QTY, 0 )) buy_sell from price_data
where trade_dt_key=p_trade_dt;




cursor cur_b is
select *
from price_new
where trade_dt_key = p_trade_dt;


type price_type is table of cur_a%ROWTYPE;

price_tab price_type;


begin


open cur_a;
fetch cur_a bulk collect into price_tab;
close cur_a;

FORALL i IN 1 .. price_tab.COUNT
update price_new pn
set price1 = -- sum(mid_prc) for the time period in --where clause
where val = price_tab(i).val
and (pn.last_minus_5 >= price_tab(i).price_dt
or
pn.last_time <= price_tab(i).tradedate);
end proc;
/
cur_a returns 300 rows. cur_b returns 3 million rows.
I need to update price_new.price1, if cur_a.val = cur_b.val and price_dt should be between last_minus_5 last_time. Can you please let me know if the usage of forall is correct? Please advice.

Hi tom,

Cant we use WHEN clause in FORALL during UPDATE also. I know we can use it in INSERT, but I wonder is there any way to use this in update. The same way like below..

forall i in 1 .. l_fileid.count
insert
when (address_flag='Y') then
into emp_addr( address, city, empno ) values ( address, city, s.currval )
select l_lname(i) lname, l_fname(i) fname, l_address(i) address, l_city(i) city, l_phone(i)
phone1,l_address_flag(i) address_flag, l_phone_flag(i) phone_flag
from dual;

Thanks.

Yeah...agreed. I want to update based on when clause in a single table.
When (ADDRESS_FLAG='Y') .... I want to update some rows...
When (ADDRESS_FLAG='N') .... I want to update another set of rows.

Thanks,
Vara.

Sorry for the delay.

Here is my example. I have a thought of using this when I'm doing a conversion program.

I picked up all the data from stage tables into plsql table types using bulk collect. After validations I have a couple of table types which stores the (1)error flag and success falg against the (2)record id's. Now I want to update the error_flag in my stage table when the plsql table returns an error for that record_id or update the same table with the derived values (which are stored in another table type) for some other columns when the record is success for that record_id.


Regards.

Hi Tom,
this construction has an excellent performance. Thanks for your detailed explanation. We use this now in Ora 9i.

In your example ... (pseudo code)
loop <cur>
fetch N records <bulk collect>

loop N times, processing N records
end loop

for all i in 1 .. N update ...
end loop <cur>

Where's the best place to put in the commit. Within each bulk block or only one commit at the end?
Is it true that with each bulk block execution runtime's getting a little higher. 500 DS in 4 minutes, next 500 DS in 6 minutes and so on. What's the reason for that?

regards, Christiane

Hi Tom,

Lovely article. Liked it.

Referring to your example, can I use an expression where the value is evaluated in the update statement itself.
Will there be any degradation in performance?

Here, I've added a substr to the ename.

58 forall i in 1 .. l_record.empno.count
59 update emp
60 set empno = l_record.empno(i),
61 ename = substr(l_record.ename(i), 1, 4),
62 job = l_record.job(i),
63 mgr = l_record.mgr(i),
64 hiredate = l_record.hiredate(i),
65 sal = l_record.sal(i),
66 comm = l_record.comm(i),
67 deptno = l_record.deptno(i)
68 where rowid = l_record.rowid(i)
69 and l_record.changed(i) = 'Y';


I'm working on something similar where I have to do something like this in the update
to_char(to_date(<varchar_column>, 'YYYYMMDDHH24MISS'), 'YYYYMMDD') || '120101'

Thank you,

Hi Tom,
I am trying to do a bulk update as below, could you please show me where I am doing the mistake-
Thanks for your help in advance.
SQL> declare
  2
  3  cursor Cur1
  4  is
  5  select distinct location_name_key,back_office_loc_code
  6  from mps.location_code_mapping a, mps.packing_list_header b
  7  where a.location_name_key = b.fob_point_key
  8  and fob_point_key is not null;
  9
 10  Type Cur1_Type
 11  is
 12  Table of Cur1%rowtype;
 13
 14  c Cur1_Type;
 15
 16  begin
 17
 18  forall x in c.FIRST..c.LAST
 19  update mps.PACKING_LIST_HEADER a set a.fob_point_code=x.back_office_loc_code
 20  where a.FOB_POINT_KEY = x.location_name_key and a.fob_point_code is null;
 21  commit;
 22  end;
 23  /
where a.FOB_POINT_KEY = x.location_name_key and a.fob_point_code is null;
                        *
ERROR at line 20:
ORA-06550: line 20, column 25:
PLS-00487: Invalid reference to variable 'X'
ORA-06550: line 20, column 25:
PL/SQL: ORA-00904: "X"."LOCATION_NAME_KEY": invalid identifier
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored

Hi Tom,
Now I am getting as  below-
SQL> declare
  2  cursor Cur1
is
  3    4  select distinct location_name_key,back_office_loc_code
  5  from mps.location_code_mapping a, mps.packing_list_header b
  6  where a.location_name_key = b.fob_point_key
  7  and fob_point_key is not null;
  8
  9  Type Cur1_Type is table of Cur1%rowtype;
 10
 11  c Cur1_Type;
 12
 13  begin
 14
 15  forall x in c.FIRST..c.LAST
 16  update mps.PACKING_LIST_HEADER set fob_point_code=c(x).back_office_loc_code
 17  where  FOB_POINT_KEY = c(x).location_name_key
 18  and    fob_point_code is null;
 19  commit;
 20  end;
 21  /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 15

Kindly tell me what is main advantages of using packages
pls dont say collection of procedures and functions ,reusabilty,modularity...........

Hello,

could the use of rowids in this case be potentially dangerous? As far as my understanding goes, the rowid can only change during a rebuild/table altering, import/export, partition move or if the table is an iot.

It's tempting to use rowid here since it's the fastest access path, but I'm curious if there could be a downside?

It's really a wonderful discussion topic. I had some concerns with bulk update which beed adressed neatly through the demonstration.

This really gave me a base for a script. Thanks

Hi Tom,
I've confusion on one of your followup with
"A reader from Chicago, IL, USA" on January 4, 2002 - 1pm Central time zone:
You mentioned -
Oh, that you could.... but you cannot....
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type empTab is table of emp%rowtype index by binary_integer;
3 l_records empTab;
4 cursor c is select * from emp;
5 begin
6 open c;
7 fetch c bulk collect into l_records;
8 close c;
9 end;
10 /
fetch c bulk collect into l_records;
*
ERROR at line 7:
ORA-06550: line 7, column 28:
PLS-00597: expression 'L_RECORDS' in the INTO list is of wrong type
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored

AND on down the line, in second followup, you mentioned -
Followup September 13, 2005 - 12pm Central time zone:

ops$tkyte@ORA9IR2> create table t as select * from all_users where 1=0;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 type array is table of t%rowtype;
3 l_data array;
4 cursor c is select * from all_users;
5 begin
6 open c;
7 loop
8 fetch c bulk collect into l_data limit 10;
9 forall i in 1 .. l_data.count
10 insert into t values l_data(i);
11 exit when c%notfound;
12 end loop;
13 close c;
14 end;
15 /

PL/SQL procedure successfully completed.

I'm confused since both examples are same and I feel that first one should not give you error as well.
please explain if I'm loosing any point here!

Hi,
I have never done bulk collect and forall updates. I followed the blog, and found it very useful, however I am trying the accomplish the following and cannot find an example (any example will also help):
My table to be updated contains about 135 million records of which about a third of these records must be updated daily therefore I need the fastest method of updating.

I need to bulk collect from the original table and update these records with values from another table (also millions of records).

Pseudo code will also help.

I created a type 'record' as per Tom's example which contains the columns that I have in a record in the table.
I am getting the PLS-00371 error (at most one declaration for 'L_UPDATE_VOUCHERS' is permitted)
My procedure:
CREATE OR REPLACE PROCEDURE BONITA_LIMITED_PREPVOUCHERS_SP AS
-- cursor of the table which contains the updated values. These values must be used to update
-- the records in table FACT_PREP_V_BONITA_small
cursor c_update_vouchers is
select * from fact_prep_v_tmp2_tb_small;

TYPE t_update_vouchers is table of c_update_vouchers%ROWTYPE INDEX BY PLS_INTEGER;
l_update_vouchers t_update_vouchers;

-- cursor of the original table that must be updated
cursor c_update_fact_vouchers is
select FACT_PREP_V_BONITA_small.*, rowid from FACT_PREP_V_BONITA_small for update;

type rowidArray is table of rowid index by binary_integer;
type update_vouchers_rec is record
(
SERIESNO NUMBER(3),
SERIALNO INTEGER,
BATCHNO INTEGER,
PACKAGENO INTEGER,
STATUS INTEGER,
DATEUSED DATE,
LOCKED NUMBER(1),
LOCKEDREASON VARCHAR2(30),
MSISDN VARCHAR2(20),
PSTATUS NUMBER(1),
PSTRANSACTION NUMBER,
PLOCKED NUMBER(1),
PLTRANSACTION NUMBER,
LOADDATE DATE,
VOUCHERID NUMBER
);

l_update_vouchers update_vouchers_rec;

-- other varaiables
l_done boolean;

BEGIN
-- open cursor of records in fact table that must be updated
open c_update_fact_vouchers;
loop

fetch c_update_fact_vouchers bulk collect into l_update_vouchers.SERIESNO, l_update_vouchers.SERIALNO,
l_update_vouchers.BATCHNO, l_update_vouchers.PACKAGENO, l_update_vouchers.STATUS,
l_update_vouchers.DATEUSED, l_update_vouchers.LOCKED, l_update_vouchers.LOCKEDREASON,
l_update_vouchers.MSISDN, l_update_vouchers.PSTATUS, l_update_vouchers.PSTRANSACTION,
l_update_vouchers.PLOCKED, l_update_vouchers.PLTRANSACTION, l_update_vouchers.LOADDATE,
l_update_vouchers.VOUCHERID limit 1000;


l_done := c_update_fact_vouchers%notfound;

-- open cursor of records in temporary table which contains updated information
open c_update_vouchers;
loop


forall i in 1 .. l_update_vouchers.VOUCHERID.count
update FACT_PREP_V_BONITA_small upd
set upd.status = c_update_vouchers.tmp5_status(i),
upd.dateused = c_update_vouchers.tmp6_dateused(i),
upd.locked = c_update_vouchers.tmp7_locked(i),
upd.lockedreason = c_update_vouchers.tmp8_lockedreason(i),
upd.msisdn = c_update_vouchers.tmp9_msisdn(i),
upd.pstatus = c_update_vouchers.tmp10_pstatus(i),
upd.pstransaction = c_update_vouchers.tmp11_pstransaction(i),
upd.plocked = c_update_vouchers.tmp12_plocked(i),
upd.pltransaction = c_update_vouchers.tmp13_pltransaction(i)
where rowid = l_update_vouchers.ROWID(i);
exit when (l_done);

end loop;
close c_update_fact_vouchers;
end loop;
close c_update_vouchers;
commit;
END;

Sorry I made a change:
forall i in 1 .. l_update_vouchers.VOUCHERID.count
update FACT_PREP_V_BONITA_small upd
set upd.status = c_update_vouchers.tmp5_status(i),
upd.dateused = c_update_vouchers.tmp6_dateused(i),
upd.locked = c_update_vouchers.tmp7_locked(i),
upd.lockedreason = c_update_vouchers.tmp8_lockedreason(i),
upd.msisdn = c_update_vouchers.tmp9_msisdn(i),
upd.pstatus = c_update_vouchers.tmp10_pstatus(i),
upd.pstransaction = c_update_vouchers.tmp11_pstransaction(i),
upd.plocked = c_update_vouchers.tmp12_plocked(i),
upd.pltransaction = c_update_vouchers.tmp13_pltransaction(i)
where rowid = l_update_vouchers.ROWID(i)
and upd.voucherid = c_update_vouchers.tmp14_voucherid;

Good day,
thank you for the reply. NO, I am not kidding. Yes I need to update about 40 million records in the table daily. Yes I can have downtime on the table that needs to be updated. The data must just be available at the start of business day (the table can be off-line from end of business day of the previous day until start of business day the next day - the table is for Business Intelligence purposes and not an real-time table).
I was also thinking of maybe truncating the table and rather inserting the records. I tried inserting new records (about 45 million at one time) and it was running for only 10 minutes to insert these records. However I would appreciate an example (or assistance to get my procedure working) as I can always use it for updates of other tables.
Kind regards