Home » SQL & PL/SQL » SQL & PL/SQL » Bulk collect for inserting million of records (Oracle 11.2,Linux 6)
Bulk collect for inserting million of records [message #669666] Sun, 06 May 2018 20:27 Go to next message
senmng
Messages: 22
Registered: April 2018
Junior Member
Hi - I have this proc which needs to insert the values into child table where the size of the parent table = 9k rows and the row count of joining tables are

table1= 1 million,table2=3 million and table3 = 4 million

here except table 3 and child tables,all other tables are residing in remote db.If that's the case,how come we declare the rowtype parameters for parent table?

Also in the for loop..will it commit for every 1 million records here and continue to load for the next 1 million records here?

CREATE OR REPLACE PROCEDURE fast_way AUTHID CURRENT_USER IS
 TYPE myarray IS TABLE OF parent%ROWTYPE;
 l_data myarray;

 CURSOR r IS
 SELECT part_num, part_name
 FROM parent@dblink join table1 on parent.id=table1.id
             join table2dblink  on table1.id=table2.id
             join table3 on table2.id=table3.id;

 BatchSize CONSTANT POSITIVE := 1000;
BEGIN
  OPEN r;
  LOOP
    FETCH r BULK COLLECT INTO l_data LIMIT BatchSize;

    FOR j IN 1 .. l_data.COUNT LOOP
      l_data(j).part_num := l_data(j).part_num * 10;
    END LOOP;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO child VALUES l_data(i);

    EXIT WHEN l_data.COUNT < BatchSize;
  END LOOP;
  COMMIT;
  CLOSE r;
END fast_way;
/
Re: Bulk collect for inserting million of records [message #669667 is a reply to message #669666] Sun, 06 May 2018 21:34 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4143119
Re: Bulk collect for inserting million of records [message #669674 is a reply to message #669667] Mon, 07 May 2018 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 13789
Registered: September 2008
Location: Rainy Manchester
Senior Member
A simple insert/select will be more efficient.
Re: Bulk collect for inserting million of records [message #669681 is a reply to message #669674] Mon, 07 May 2018 08:06 Go to previous messageGo to next message
senmng
Messages: 22
Registered: April 2018
Junior Member
Yes Cookie Master,i tried with INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE and it took more than 4 hours to run and ended with 0 records inserted.
Re: Bulk collect for inserting million of records [message #669682 is a reply to message #669681] Mon, 07 May 2018 08:09 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
senmng wrote on Mon, 07 May 2018 06:06
Yes Cookie Master,i tried with INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE and it took more than 4 hours to run and ended with 0 records inserted.
Statement above is arguably false.
Other session can NOT see uncommitted rows in table so you have no direct way to determine how many rows had been processed when you aborted the session.

How much REDO had been produced by active session when abort was done?
Re: Bulk collect for inserting million of records [message #669689 is a reply to message #669682] Tue, 08 May 2018 02:54 Go to previous messageGo to next message
cookiemonster
Messages: 13789
Registered: September 2008
Location: Rainy Manchester
Senior Member
And using bulk collect instead will probably take longer (and if it doesn't that's just luck).
Re: Bulk collect for inserting million of records [message #669690 is a reply to message #669689] Tue, 08 May 2018 03:00 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
It does take longer, but the strong argument used in its favour (I'm on the fence personally) is that doing a bulk collect commit allows restart-ability (in the sense it is going to pick up almost where it left off) should the job fail partway through. In an ideal world it won't, but life is rarely so convenient Smile

It's not sufficiently slower that I shout at people for doing it at least.

[Updated on: Tue, 08 May 2018 03:01]

Report message to a moderator

Re: Bulk collect for inserting million of records [message #669751 is a reply to message #669666] Thu, 10 May 2018 06:50 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi senmng,

I believe, In this case you can unload data into .csv/.dat/.txt etc file and then load data using sqlldr. I hope this can be the good approach.

[Updated on: Thu, 10 May 2018 06:51]

Report message to a moderator

Re: Bulk collect for inserting million of records [message #669752 is a reply to message #669751] Thu, 10 May 2018 07:06 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
msol25 wrote on Thu, 10 May 2018 04:50
Hi senmng,

I believe, In this case you can unload data into .csv/.dat/.txt etc file and then load data using sqlldr. I hope this can be the good approach.

the same can be done with any PL/SQL
Re: Bulk collect for inserting million of records [message #669785 is a reply to message #669752] Fri, 11 May 2018 08:39 Go to previous messageGo to next message
senmng
Messages: 22
Registered: April 2018
Junior Member
Hi Swan - Can we create a view in the source remote database so that we can store the results with joined and distinct values and then refer the same view in the local database ?
Re: Bulk collect for inserting million of records [message #669786 is a reply to message #669785] Fri, 11 May 2018 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
Who exactly is "we"?
I give up.
Can you?

BTW - a view is just stored SQL & as such it really contains no actual data itself.

Why you do continue to post question here rather than produce actual SQL statements & test to see if they do as you desire?
Re: Bulk collect for inserting million of records [message #669787 is a reply to message #669785] Fri, 11 May 2018 09:10 Go to previous message
cookiemonster
Messages: 13789
Registered: September 2008
Location: Rainy Manchester
Senior Member
Creating a materialized view in the remote DB may well help.
Previous Topic: Outer Join Question
Next Topic: SELECT WITH TIMESTAMP AND SYSDATE
Goto Forum:
  


Current Time: Wed Jan 29 01:42:01 CST 2020