bulk collect into a cursor and compare values in cursor so as to insert

I want to insert values into table a from table b where the records do not exist already in table a.  The problem is that there are over 1 million records in table b to compare to table a, which contains 138,000 and is constantly growing.  The problem is when I try to:

insert into table_a
(
  issue_no
)
(
 select
  issue_no
from table_b
where issue_no not in (select distinct issue_no from table_a)
)

as you can imagine it takes forever. Over 18 minutes later I stopped the proc because we have to get this to execute every 30 minutes.  So I have tried to do a bulk collect into to speed up the process but this procedure, while it completes, does not insert any records into table_a.


CREATE OR REPLACE procedure asp_owner.SP_Test

as

type ridArray is table of rowid;
type vcArray is table of table_a.issue_no%type;

v_RowIds  ridArray;
v_IssueNo vcArray;

cursor c is select rowid, issue_no from table_a;

begin

open c;

loop

fetch c bulk collect into v_RowIds, v_IssueNo;
forall i in 1 .. v_RowIds.count

insert into table_a
(
  issue_no
)
(
select
  I.issue_no
from table_b      I
where I.ISSUE_NO not in (v_IssueNo(i))
);

commit;

exit when c%notfound;

end loop;

close c;

end;
/

Any help would be greatly appreciated.

Thanks in advance,

B
LVL 1
cyimxtckAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ishandoConnect With a Mentor Commented:
Something a bit more like this should be more efficient.

I think your procedure has the cursor on the wrong table.
Also, is the issue_no field indexed?
create or replace procedure asp_owner.SP_Test
as
  type vcArray is table of table_b.issue_no%type;
  v_IssueNo vcArray;
  
  maxrecs integer 10000;
 
  cursor c is 
    select issue_no from table_b b
    where not exists 
      (select 1 from table_a a where a.issue_no = b.issue_no);
 
begin
  open c;
  loop
    fetch c bulk collect into v_IssueNo limit 10000;
    exit when not v_IssueNo.exists(1);
 
    forall i in v_IssueNo.first .. v_IssueNo.last
      insert into table_a (issue_no)
      values (v_IssueNO(i));
 
    commit;
  end loop;
  close c;
end;
/

Open in new window

0
 
cyimxtckAuthor Commented:
Yes issue number is indexed; my first thought was that this was not an indexed column as well.

Your procedure was perfect; inserted 26,219 rows in 2 seconds!  

PERFECTO!!!

Thanks for your help,

B
0
 
cyimxtckAuthor Commented:
As good as it gets!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.