Any Way To No Commit on Every Insert in Cursor Loop?

[wjramsey]

I have a 5 Million row table that I'm trying to recreate as a partitioned table. Basically I've created a matching, partitioned table and am using a cursor loop to read a row from the original table and inserting it into the new table. It is doing a commit after each insert which is slowing things down. Is there any way inside of a simple cursor loop to tell to only commit every X number of rows?

Here is the loop

DECLARE

CURSOR c_Customer_Dim IS
SELECT * FROM MKDM.CUSTOMER_DIM;

v_Customer_Data c_Customer_Dim%ROWTYPE;

BEGIN

OPEN c_Customer_Dim;

FETCH c_Customer_Dim into v_Customer_Data;

WHILE c_Customer_Dim%FOUND LOOP

INSERT INTO MKDM.PART_CUSTOMER_DIM (
CUSTOMER_ID,
SOURCE_KEY,
...
OLD_HHID,
CTYPE_PENR)
VALUES (
v_Customer_Data.CUSTOMER_ID,
v_Customer_Data.SOURCE_KEY,
...
v_Customer_Data.OLD_HHID,
v_Customer_Data.CTYPE_PENR);

commit;

FETCH c_Customer_Dim INTO v_Customer_Data;
END LOOP;
CLOSE c_Customer_Dim;
commit;
end;
/

[Edited by wjramsey on 03-27-2001 at 07:37 AM]

[chrisrlong]

Of course. Just set up a counter and commit every x rows.


DECLARE
x NUMBER
...
BEGIN
x := 0
...
WHILE ...
x:=x+1;
INSERT...
IF ( mod(x, 100) = 0) THEN
COMMIT;
END IF;
...


- Chris

[gpsingh]

Why not use , alter table ...exchange partition option.

[mber]

Hi,

Actually, it will slow things down a little bit by using mod, you can just use counter to determine the commit point, somthing like:
If counter = X THEN ----- X rows to commit
counter := 0;
commit;
END IF;
counter := counter + 1;
.....

Take care