Collection count high in Memory [message #577278] |
Wed, 13 February 2013 23:35 |
|
rishwinger
Messages: 132 Registered: November 2011
|
Senior Member |
|
|
Hi Experts,
I have two tables with same columns(15 of them), I am trying to find difference between two tables using minus operator and then insert in stage table using below code
Issue is table1 has 50 million records
table2 is empty
so when first time when we execute this v_collection1,v_collection2 collection will have 50 million records in it which will go in memory, I think this is not good, because going in memory will eat memory and resources while sorting and other activities ?
After fetching records in collection we are inserting that in stage table and then COMMIT so i think that
wont be good because commiting 50 million will generate large amount of redo?
Please explain me repercussions of below approach and alternative approach?
below is snippet of my code
DECLARE
type lst_collection1
IS
TABLE OF table1.col1%type INDEX BY binary_integer;
type lst_collection2
IS
TABLE OF table1.col2%type INDEX BY binary_integer;
v_collection1 lst_collection1;
v_collection2 lst_collection2;
SELECT col1,
col2 bulk collect
INTO v_collection1,
v_collection2
FROM
(SELECT col1,col2.....col15 FROM table1
MINUS
SELECT col1,col2.....col15 FROM table2
);
FOR i IN 1..v_collection2.count
LOOP
INSERT INTO stage TABLE VALUES
(v_collection1(i),v_collection2(i)
);
END LOOP;
COMMIT;
thanks in advance
|
|
|
|
|
|
Re: Collection count high in Memory [message #577290 is a reply to message #577283] |
Thu, 14 February 2013 01:16 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:wont be good because commiting 50 million will generate large amount of redo?
Wrong. The size of the transaction does not change the size of redo for commit: always 20-30 bytes.
The DML inside the transaction generates redo but you do it in one or multiple transactions does not change the size of redo generated (roughly, as each commit implies some little bit of work, like blocks cleaning).
So committing several times generate more redo and even "suspend" for longer all other modifications in the database during these commits.
Regards
Michel
|
|
|