Need Help With Commit 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;
/

[shawish_sababa]

DECLARE

v_help number :=0.0;

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);

v_help := v_help + 1.0;

if v_help = 10000
then commit;
else null;
end if;

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

[akkerend]

After the commit in the loop, reset the counter v_help.

...
if v_help = 10000
then
commit;
v_help := 0.0;
else
null;
end if;
...

[m1l]

You could also do it in sql, something like this

Set copycommit 10;
copy from user/password@database to user/password@database insert table1 using select * from table2

I am pretty sure that the "copy from ...." command as to be all on the same line.

This way is easier and takes a lot less resource.

[m1l]

PS

If you do want to use PL/SQL, I would say it is better to use a CURSOR ... FOR ... LOOP, so the code would look like

DECLARE
v_help number :=0.0;
--
CURSOR c_Customer_Dim IS
SELECT *
FROM MKDM.CUSTOMER_DIM;
BEGIN
FOR v_customer_data in c_customer_dim 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);
--
v_help := v_help + 1.0;
if v_help = 10000 then
commit;
END IF;
END LOOP;
commit;
end;
/



It's a lot easier to read nd you don't have all the opening a closing of cursors of defining of the record.

Mike

[wjramsey]

Thanks for the help!!!!

It's working like a charm now

Joe

[jmodic]

I'm adding this just for the sake of "elegant and efficient coding" (and because you've gotten two different PL/SQL block examples and in both authors have forgotten to reset the counter variables, which proofs such coding is not the best practice...):

You dont need a special variable to count records fetched by a cursor. PL/SQL corsor has an inbuild attribute that tells you this count: %ROWCOUNT. With it and a simple use of MOD() function you could commit every 1000 fetched records with the following:

.....
FOR r1 IN c1 LOOP
.....
IF MOD(c1%ROWCOUNT,1000) = 0 THEN COMMIT;
END IF;
END LOOP;

No need to declare a special varible for the counter, no need to increment that variable, no need to reset it after each commit. The code is more elegant and more efficient.

HTH,

[amar]

Just my curiosity:-)

Which one is more efficient?

v_help := v_help + 1

or

MOD(c1%ROWCOUNT,1000)


assuming that both would be executed 200 million times.


-amar

[mber]

The first one is more efficient, not fuction call overhead.

Take care

[marist89]

Originally posted by jmodic

.....
FOR r1 IN c1 LOOP
.....
IF MOD(c1%ROWCOUNT,1000) = 0 THEN COMMIT;
END IF;
END LOOP;

Another pearl of wisdom from jmodic. Good job...