Transform Cursor to Bulk

[diepa]

HI, I'm trying yo improve the performance of the golowing piece of code.
Here I'm archiving Items that are done processing to Archive Tables.
I believe that if I use BULK INSERTS/SELECTS/UPDATES this process will move much faster.

Can someone suggest me a way to do it?
Thanks

declare
cursor curflowdoc is select docid, state, other, rowid
from diego_parent
where state = 200;
show_value varchar2(40);
v_payee varchar2(40);
begin
for cur1 in curflowdoc loop
--Archive child items
insert into diego_child_arch
select * from diego_child
where docid = cur1.docid;

--Archive Parent items
insert into diego_parent_arch
select * from diego_parent
where rowid = cur1.rowid;

show_value := to_char(cur1.docid);
dbms_output.put_line('Deleting ' || show_value);

---More selects and updates here on other tables here based on that DOCID
select payee into v_PAYEE
from payee_T
where other = cur1.other;

insert into temp(a,b,c) values(cur1.docid, cur1.state, v_payee);



--Delete Archived child items
delete from diego_child
where docid = cur1.docid;

--Delete Archived Parent items
delete from diego_parent
where rowid = cur1.rowid;
end loop;
end;

 

[carp]

I think your performance might improve dramatically if you eliminate the cursor altogether.
Try a SQL-based approach inside your block:

BEGIN
INSERT INTO diego_child_arch
SELECT * from diego_child
WHERE docid IN
(SELECT docid FROM diego_parent WHERE state = 200);

INSERT INTO diego_parent_arch
SELECT * FROM diego_parent
WHERE state = 200;
.
.
.
END;

 

[diepa]

Yes but the problem is that I need to perform some "tows touch", that's why I need to go row by row.
Any other suggestions ?

 

[jaggiebunnet]

tows touch ?

 

[carp]

My question exactly, jaggiebunnet!

In addition, I'm not aware of any "bulk insert/update" mechanisms, just BULK COLLECT, which is for loading rows into a PL/SQL table. This doesn't mean they don't exist, but I don't think they do.

But I KNOW I don't see any "tows touch"ing in

for cur1 in curflowdoc loop
--Archive child items
insert into diego_child_arch
select * from diego_child
where docid = cur1.docid;

--Archive Parent items
insert into diego_parent_arch
select * from diego_parent
where rowid = cur1.rowid;
[/qoute]
If by "tows touch" you mean "I need to display which rows were deleted" (specifically,
dbms_output.put_line('Deleting ' || show_value), then I see your problem. But I don't think BULK COLLECT can help you much here - in fact, going over to a PL/SQL table could actually bog down your performance!