Need Help With Commit in Cursor Loop

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

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

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

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.

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

Thanks for the help!!!!

It's working like a charm now :)

Joe

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,

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

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

Take care

Quote:

---

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...

Quote:

---

Originally posted by mber
The first one is more efficient, not fuction call overhead.

Take care

---

Wrong, the second one is more efficient!

Remember that MOD is a built-in function, it will require no more of procesor's time as incrementing a variable! None of those two two operations will require more than one processor tick (is this the right term for granularity of processor's time in which a single instruction can be executed?).

But on 200 M row table and commiting every 1.000 records, the variant with the explicit counter variable will have to execute "v_help := 0;" 200.000 times! This is overhead!

In any case, I don't belive the difference in the performance on either two cases will be mesurable at all! The point is, the use of %ROWCOUNT is much less error-prone than maintaining a special variable for this. I'm sure somewhere in the cours of our PL/SQL careers every one of us have made the same error as both posters of PL/SQL code have made - to forget to reset the counter. With MOD() and %ROWCOUNT you simply can't forget, as there is nothing to reset.

thank you jmodic. it was very interesting to read.

In my defence, I didn't forget to reset the counter, I had copied the code from the person above only to show how a CURSOR ... FOR ... LOOP works. I still am of the opinion that the SQL COPY command is the best option but I am open to being shot down in flames.

Quote:

---

Originally posted by m1l
In my defence, I didn't forget to reset the counter, I had copied the code from the person above only to show how a CURSOR ... FOR ... LOOP works. I still am of the opinion that the SQL COPY command is the best option but I am open to being shot down in flames.

---

I agrea with you about the COPY command and it's efficiency and usefullness. However it has one limitation: it is only a SQL*Plus command (not a SQL command, as you said), so you can execute it only from SQL*Plus. This is pretty severe limitation, mybe not in this particular case, but in general.