Home » RDBMS Server » Performance Tuning » Collection count high in Memory (11.2.0.3.0)
Collection count high in Memory [message #577278] Wed, 13 February 2013 23:35 Go to next message
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 #577280 is a reply to message #577278] Thu, 14 February 2013 00:01 Go to previous messageGo to next message
Littlefoot
Messages: 19788
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why wouldn't you leave PL/SQL alone and use SQL only?
insert into stage_table
select * from table1
minus
select * from table2
(substitute "*" with a column list).
Re: Collection count high in Memory [message #577283 is a reply to message #577280] Thu, 14 February 2013 00:40 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Littlefoot no words just Brilliant Man!!!
Thanks

Since u have already resolved my first problem please give me some suggestion for below issue also

Quote:
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?
Re: Collection count high in Memory [message #577285 is a reply to message #577283] Thu, 14 February 2013 00:47 Go to previous messageGo to next message
Littlefoot
Messages: 19788
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know; I don't work with that much data at once. You'll have to wait for someone else's opinion.

However: as you said, it will commit 50 million records only once. Every subsequent execution of the same code will use ... well, most probably much less records (can't tell how much, but you might know it).
Re: Collection count high in Memory [message #577290 is a reply to message #577283] Thu, 14 February 2013 01:16 Go to previous message
Michel Cadot
Messages: 59784
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
Previous Topic: LONG to CLOB conversion in huge tables
Next Topic: Improve DDL operation performance (2 Merged)
Goto Forum:
  


Current Time: Wed Nov 26 03:18:29 CST 2014

Total time taken to generate the page: 0.16034 seconds