-
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
-
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
_________________________
Joe Ramsey
Senior Database Administrator
dbaDirect, Inc.
(877)687-3227
-
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,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
-
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...
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|