Need Help With Commit in Cursor Loop
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Need Help With Commit in Cursor Loop

  1. #1
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    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

  2. #2
    Join Date
    Jul 2000
    Posts
    243
    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;
    /

  3. #3
    Join Date
    Jul 2000
    Posts
    296
    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;
    ...

  4. #4
    Join Date
    Oct 2000
    Posts
    90
    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.

  5. #5
    Join Date
    Oct 2000
    Posts
    90
    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


  6. #6
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Thanks for the help!!!!

    It's working like a charm now

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    Mar 2001
    Posts
    314
    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

  9. #9
    Join Date
    Oct 2000
    Posts
    123
    The first one is more efficient, not fuction call overhead.

    Take care

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width