Solved

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

Posted on 2008-06-25
3
922 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
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DBLINKS From 11g to 8i 3 47
having some issue on pl sql procedure 1 23
Database Design Dilemma 6 58
Oracle create type table from existing table%rowtype ? 6 34
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

809 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