Woot4Moo Woot4Moo - 1 month ago 8
SQL Question

Oracle SQL insert large data set efficiently via cursor use and single commit

The following SQL generates all matching records between two tables that have identical schemas and then proceeds to iterate over the cursor that stores this result set. I do a row by row insert with a commit at the end of this function. My question is how can I get the maximum performance from this type of query? Code follows:

BEGIN
DECLARE
CURSOR foo IS
SELECT * FROM tableOne to
WHERE EXISTS (SELECT * FROM tableTwo tt
WHERE TO.FOO = TT.FOO
AND TO.BAR = TT.BAR); --THIS TAKES 5 MINUTES (66 MILLION ROWS)
BEGIN
FOR nextFoo IN foo
LOOP
INSERT INTO tracker t
(id,foo,bar,baz)
VALUES(trackerSequence.nextval, nextFoo.foo,nextFoo.bar,nextFoo.baz);
END LOOP;
COMMIT;
END;
END;


This query can take upwards of an hour and I am trying to reduce the time cost associated with it. I will be processing 140 million records in general so I am expecting to double the amount of time this process takes. All columns are indexed.

Version information:

10g 10.2

Answer

I have found the following will do 130 million inserts in about 49 minutes.

INSERT INTO tracker t  
    SELECT * FROM tableOne to  
        WHERE NOT EXISTS (SELECT * FROM tableTwo tt  
                       WHERE TO.FOO = TT.FOO  
                       AND TO.BAR = TT.BAR);