Solved

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

Posted on 2008-06-25
3
924 Views
Last Modified: 2013-12-07
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
Comment
Question by:cyimxtck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
ishando earned 500 total points
ID: 21871888
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
 

Author Comment

by:cyimxtck
ID: 21873417
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
 

Author Closing Comment

by:cyimxtck
ID: 31470814
As good as it gets!
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question