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;
/
_________________________
Joe Ramsey
Senior Database Administrator
dbaDirect, Inc.
(877)687-3227