1

I am trying to copied records from one table to another as fast as possible.

Currently I have a simple cursor loop similiar to this:

FOR rec IN source_cursor LOOP
   INSERT INTO destination (a, b) VALUES (rec.a, rec.b)
END LOOP;

I want to speed it up to be super fast so am trying some BULK operations (a BULK FETCH, then a FORALL insert):

Here is what I have for the bulk select / forall insert.

DECLARE 
  TYPE t__event_rows IS TABLE OF _event%ROWTYPE;
  v__event_rows t__event_rows;

  CURSOR c__events IS
    SELECT * FROM _EVENT ORDER BY MESSAGE_ID;
BEGIN
  OPEN c__events;
  LOOP
    FETCH c__events BULK COLLECT INTO v__event_rows LIMIT 10000;  -- limit to 10k to avoid out of memory

    EXIT WHEN c__events%NOTFOUND;

    FORALL i IN 1..v__event_rows.COUNT SAVE EXCEPTIONS
      INSERT INTO destinatoin
        ( col1, col2, a_sequence) 
        VALUES 
        (  v__event_rows(i).col1,  v__event_rows(i).col2, SOMESEQEUENCE.NEXTVAL );


  END LOOP;
  CLOSE c__events;


END;

My problem is that I'm not seeing any big gains in performance so far. From what I read it should be 10x-100x faster.

Am I missing a bottleneck here somewhere?

Will
  • 1,622
  • 4
  • 25
  • 39
  • 1
    for 100,000 rows, it takes approximately 300 (!) seconds even with the bulk insert – Will Oct 09 '10 at 00:17
  • 4
    Is there a reason that you have an ORDER BY in your SELECT? It could be rather expensive to sort 100,000 rows and that doesn't seem to be necessary. Also your %NOTFOUND check needs to happen after your FORALL-- otherwise, if you fetch less than 10,000 rows in the last iteration, you won't insert those rows. – Justin Cave Oct 09 '10 at 00:29
  • thanks! i will removing the order by, but i dont think its going to get me quite to the speed im looking for... i will try to get a tkprof analysis and update this later. good catch with the %NOTFOUND – Will Oct 09 '10 at 03:12
  • 2
    Do you have a trigger in your destination table? Also, if you're just copying data from one table to another, can you use a SQL statement instead of PL/SQL? – Jon Heller Oct 09 '10 at 03:15
  • On a related note: http://stackoverflow.com/questions/987013/bulk-insert-into-oracle-database-which-is-better-for-cursor-loop-or-a-simple-se – Sathyajith Bhat Oct 11 '10 at 16:01

2 Answers2

7

The only benefit your code has over a simple INSERT+SELECT is that you save exceptions, plus (as Justin points out) you have a pointless ORDER BY which is making it do a whole lot of meaningless work. You then don't have any code to do anything with the exceptions that were saved, anyway.

I'd just implement it as a INSERT+SELECT.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
0

You donot have to use loops unnecessarily until it is required in the coding itself.

Bikrant Kumar Sahoo
  • 1