Any Way To No Commit on Every Insert in Cursor Loop?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Any Way To No Commit on Every Insert 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;
    /

    [Edited by wjramsey on 03-27-2001 at 07:37 AM]
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Of course. Just set up a counter and commit every x rows.


    DECLARE
    x NUMBER
    ...
    BEGIN
    x := 0
    ...
    WHILE ...
    x:=x+1;
    INSERT...
    IF ( mod(x, 100) = 0) THEN
    COMMIT;
    END IF;
    ...


    - Chris

  3. #3
    Join Date
    Feb 2001
    Posts
    389
    Why not use , alter table ...exchange partition option.

  4. #4
    Join Date
    Oct 2000
    Posts
    123
    Hi,

    Actually, it will slow things down a little bit by using mod, you can just use counter to determine the commit point, somthing like:
    If counter = X THEN ----- X rows to commit
    counter := 0;
    commit;
    END IF;
    counter := counter + 1;
    .....

    Take care

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