Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Currently in Delaware, US.
    Posts
    5

    Unanswered: Delete From Table

    Hi,

    I have a procedure which has a cursor on two tables as below.

    CURSOR xyz_cur IS
    SELECT A.val
    FROM note A,
    (SELECT distinct aid ID FROM junk
    UNION ALL
    SELECT distinct bid ID FROM junk
    UNION ALL
    SELECT distinct cid ID FROM junk
    union all
    select distinct did ID FROM junk) B
    WHERE A.ID = B.ID(+)
    AND B.ID IS NULL;

    For each value of the cursor record, it deletes the rows in a table as follows:

    FOR xyz_rec IN xyz_cur
    LOOP
    DELETE FROM note WHERE val = xyz_rec.val;
    nRowsDeleted := nRowsDeleted + 1;
    IF (MOD(nRowsDeleted, 1000) = 0)
    THEN
    COMMIT;
    END IF;
    END LOOP;

    The select statement works fine and gives result in hardly 1 min. But, the delete takes a lo...ng time, even it hangs sometime. Does anybody know what could be the reason for this weird behaviour?

    Thanks,
    Luke.

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Delete From Table

    Could be the number of records your trying to delete. Could be the size of your rollback segments.

    Instead of deleting 1 record at a time, why not try a delete statement using a join?

    If you have a TON of records to delete, might want to try something like this:

    PHP Code:
    Cursor c_count is
       Select count
    (*)
       
    From TableA
       Where Not Exists
                
    (Select 'Y'
                 
    From Tableb
                 Where Tableb
    .ID TableA.ID);

    lv_cnt              Number;
    lv_str               Varchar2(500);
    lv_commit_cnt  Number := 5000;
    Begin

    Open c_count
    ;
    Fetch into lv_cnt;
    -- 
    test for error here
    Close c_count
    ;

    If 
    lv_cnt <= lv_commit_cnt Then
       lv_commit_cnt 
    := lv_commit_cnt 5;
    End If;

    for 
    .. (lv_cnt lv_commit_cnt) + 1 Loop

    lv_str 
    := 'Delete * '||
                 
    'From TableA '||
                 
    'Where Not Exists  '||
                            
    '(Select 1 '||
                            
    'From Tableb '||
                            
    'Where Tableb.ID = TableA.ID) '||
                            
    'And Rownum < '||lv_commit_cnt;

    Execute Immediate lv_str;
    commit;

    End loop

  3. #3
    Join Date
    Nov 2003
    Posts
    1

    Lightbulb Re: Delete From Table

    FOR xyz_rec IN xyz_cur
    LOOP
    DELETE FROM note WHERE val = xyz_rec.val;
    nRowsDeleted := nRowsDeleted + 1;
    IF (MOD(nRowsDeleted, 1000) = 0)
    THEN
    COMMIT;
    END IF;
    END LOOP;
    you commit only when there is 1000 rows deleted... so if somebody read one row you try to delete... you have to wait this rows is free... try to force commit evry time you delete ;-) and... sorry for my poor english ;-)

Posting Permissions

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