Home » SQL & PL/SQL » SQL & PL/SQL » BULK Collect Inside FOR LOOP? (8i/Unix to 10G/Linux)
BULK Collect Inside FOR LOOP? [message #427203] Wed, 21 October 2009 09:37 Go to next message
adminme
Messages: 31
Registered: May 2006
Member
Hello;

I am using this procedure to move 20k records from one box to another. I want to put an outside loop in which runs the statement inside for each year (about 2k records,)commit and move to the next year, commit and so on.

I tried nesting cursors by the year from the outer cursor is out of scope when used in the inner cursor's where clause. It won't compile. PLS-00103: Encountered the symbol "COLLECT" when expecting one of the following:

:= . ( @ % ;

When I ran the procedure w/out nesting cursors it moved the records in 35 min but when I select count(*) from the destination table it took 1:30 min.

Do I need another commit? How can I limit the bulk collect to a given year.

Thank you

CREATE OR REPLACE PROCEDURE AMATSON.RELOAD_AE (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF FSADM.ft_acct_xtract@admprd8i.tceq.state.tx.us%ROWTYPE;
ae_data ARRAY;

--CURSOR C_FY IS
--SELECT DISTINCT(FY)
--FROM FSADM.ft_acct_xtract@admprd8i.tceq.state.tx.us;


CURSOR C_AE IS
SELECT *
FROM FSADM.ft_acct_xtract@admprd8i.tceq.state.tx.us;

BEGIN

--FOR v_fy in c_fy LOOP

OPEN C_AE;

LOOP
FETCH C_AE BULK COLLECT INTO ae_data LIMIT p_array_size;

FORALL i IN 1..ae_data.COUNT
INSERT /*+ APPEND */ INTO AMATSON.ft_acct_xtract_temp VALUES ae_data(i);

EXIT WHEN C_AE%NOTFOUND;

END LOOP;
COMMIT;

CLOSE C_AE;

--END LOOP;

END RELOAD_AE;
/

Re: BULK Collect Inside FOR LOOP? [message #427206 is a reply to message #427203] Wed, 21 October 2009 09:44 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

How can I limit the bulk collect to a given year.



If I am correct (Please correct if I am wrong )You can load with Direct Load Insert using filter LAST UPDATE TIMESTAMP


[Updated on: Wed, 21 October 2009 09:45]

Report message to a moderator

Re: BULK Collect Inside FOR LOOP? [message #427209 is a reply to message #427203] Wed, 21 October 2009 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why not just do a straight insert as select?

No loops, no bulk collect - it'll be a lot faster.
Re: BULK Collect Inside FOR LOOP? [message #427210 is a reply to message #427206] Wed, 21 October 2009 09:47 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
Are you referring to a simple insert statement? What do you mean exactly by direct load insert?

Simple example would be helpful.
Re: BULK Collect Inside FOR LOOP? [message #427215 is a reply to message #427210] Wed, 21 October 2009 09:55 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
adminme wrote on Wed, 21 October 2009 09:47
Are you referring to a simple insert statement? What do you mean exactly by direct load insert?

Simple example would be helpful.


Here it is

INSERT /*+ APPEND */ INTO emp_new 
   SELECT * FROM emp where lats_updt_tmsp < SYSDATE - 1; 
COMMIT; 


Re: BULK Collect Inside FOR LOOP? [message #427216 is a reply to message #427203] Wed, 21 October 2009 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes a single insert statement.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: BULK Collect Inside FOR LOOP? [message #427233 is a reply to message #427216] Wed, 21 October 2009 10:48 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
Thanks for the input and example.

When i did;

INSERT INTO table
(Select * from table@dblink WHERE year = '2010';

It correctly loaded 350,000 rows in 7:45sec. I'm not sure i'd get a performance improvement inserting 20k rows this way.

I attempted this, if I understood Its me ved correctly...

INSERT /*+ APPEND */ INTO ft_acct_xtract_temp_3
SELECT * FROM ft_acct_xtract_temp where fy < fy - 1;

Obviously I didn't get it- ha ha.

The year is a 4 digit number in the table- there are no timestamps. Each year is approx 2k rows. No LOOP? We need to move these records each morning as efficiently as possible for several weeks. Database export/import process is very poor.

Did i miss something or if you other comments please reply.

If not- thanks so much for your time.
Re: BULK Collect Inside FOR LOOP? [message #427236 is a reply to message #427233] Wed, 21 October 2009 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
INSERT INTO table
(Select * from table@dblink WHERE year = '2010';

It correctly loaded 350,000 rows in 7:45sec.

So you have your solution, doesn't it? If year is a number remove quote from '2010' and index the year in your table you will may go faster.

If you think this time is too big, activate a sql trace and see when you spend the time, maybe it is on network.

Regards
Michel

Re: BULK Collect Inside FOR LOOP? [message #427238 is a reply to message #427203] Wed, 21 October 2009 10:56 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Surely all you need is
INSERT INTO table
(Select * from table@dblink WHERE year between <start_year> and <end_year>);
Re: BULK Collect Inside FOR LOOP? [message #427241 is a reply to message #427238] Wed, 21 October 2009 11:03 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
I need to use a procedure from which I can run in CRON daily.
This procedure will select & insert all 20,000000 rows across a dblink which contain 10 years of data.

Looking for better performance.

thanks again
Re: BULK Collect Inside FOR LOOP? [message #427242 is a reply to message #427238] Wed, 21 October 2009 11:04 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
range condition produces full table scan- no?
Re: BULK Collect Inside FOR LOOP? [message #427244 is a reply to message #427203] Wed, 21 October 2009 11:08 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not necessarily.
How many records are you trying to insert here?
Original you said 20K rows, now your saying 20,000000 or 20M rows, which is it?
And how many records are in the source table?
Re: BULK Collect Inside FOR LOOP? [message #427255 is a reply to message #427244] Wed, 21 October 2009 11:57 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
cookiemonster-

Sorry about that. I am selecting 20 million rows from source and inserting them into an empty table w/no constraints or indexes. What's more is needing to rebuild indexes afterwards.

I ran this in 24:45sec and inserted 10,500,00:

CREATE OR REPLACE PROCEDURE LOAD_AE_3 IS

BEGIN

INSERT INTO AMATSON.ft_acct_xtract_temp_3
(SELECT * FROM FSADM.ft_acct_xtract@admprd8i.tceq.state.tx.us WHERE fy > '2005');

COMMIT;

END;
/
Re: BULK Collect Inside FOR LOOP? [message #427266 is a reply to message #427203] Wed, 21 October 2009 12:21 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
25 mins for 10 million rows accross a db link is not necessarily bad. Could be it's the fastest you're going to get.
Like Michel suggested earlier you should trace the session and see where the times being spent - it could well be network rather than anything in the db.

Also you still haven't told us how many rows are in the source table, just how many you're selecting.
Re: BULK Collect Inside FOR LOOP? [message #427269 is a reply to message #427266] Wed, 21 October 2009 12:27 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
20,000,000 rows

Thanks Guys!! Have a great day.
Re: BULK Collect Inside FOR LOOP? [message #427272 is a reply to message #427269] Wed, 21 October 2009 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
adminme wrote on Wed, 21 October 2009 19:27
20,000,000 rows

Thanks Guys!! Have a great day.

Does this mean you solvzd your issue?

Regards
Michel

Re: BULK Collect Inside FOR LOOP? [message #427278 is a reply to message #427272] Wed, 21 October 2009 12:47 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
I tend to agree with cookiemonster that I got good results initially.

I was still interested in committing records for every year in the table. I want to test my original BULK procedures timing more.

If I get consistent timing results I believe that will work. If I don't then i'm back to square one.
Re: BULK Collect Inside FOR LOOP? [message #427279 is a reply to message #427278] Wed, 21 October 2009 13:09 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

Quote:
I tend to agree with cookiemonster that I got good results initially.

I was still interested in committing records for every year in the table. I want to test my original BULK procedures timing more.

If I get consistent timing results I believe that will work. If I don't then i'm back to square one.





If I am not wrong, everyone here suggested you to use single insert (Direct Load) instead of Bulk collect

Previous Topic: before insert trigger
Next Topic: insert versus merge
Goto Forum:
  


Current Time: Tue Sep 27 05:56:16 CDT 2016

Total time taken to generate the page: 0.08747 seconds