| BULK Collect Inside FOR LOOP? [message #427203] |
Wed, 21 October 2009 09:37  |
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 #427215 is a reply to message #427210] |
Wed, 21 October 2009 09:55  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
adminme wrote on Wed, 21 October 2009 09:47Are 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 #427233 is a reply to message #427216] |
Wed, 21 October 2009 10:48 |
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 #427241 is a reply to message #427238] |
Wed, 21 October 2009 11:03 |
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 #427244 is a reply to message #427203] |
Wed, 21 October 2009 11:08 |
cookiemonster
Messages: 13968
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 |
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 |
cookiemonster
Messages: 13968
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 #427278 is a reply to message #427272] |
Wed, 21 October 2009 12:47 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
