fastest routine to copy data

[rbrooker]

hi,

i have a table that has 1.8 billion rows that i need to copy from "HERE" to "THERE" in the same database. in your learned opinions, what would be the fastest means of doing this? As to why, i am changing the partitioning from range hash to range list.

what i have is :

DECLARE
   TYPE ARRAY IS TABLE OF owner.transactions%ROWTYPE;
   l_data ARRAY;

   CURSOR c1 IS
      SELECT /*+ PARALLEL( a, 8 ) */ *
      FROM owner.transactions a;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 BULK COLLECT
         INTO l_data LIMIT 200000;

      FORALL i IN INDICES OF l_data
         INSERT INTO owner.new_transactions
         VALUES l_data(i);
      COMMIT;
      EXIT WHEN c1%NOTFOUND;
   END LOOP;
   COMMIT;
END;
/

any ideas in how to improve it?

thanks.

 

[Marco van der Linden]

You might want to check asktom.oracle.com. There are some threads there which pertain to the same kind of question/problem. Generally the answer to your question is that any kind of PL/SQL causes a loss of performance. So the 'quickest' way of doing this is 'insert into owner.new_transactions (select * from owner.transactions'. Just make sure your rollback segment(s) are large enough.