[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 940
  • Last Modified:

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
0
cyimxtck
Asked:
cyimxtck
  • 2
1 Solution
 
ishandoCommented:
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now