This discussion is archived
5 Replies Latest reply: Jan 21, 2013 12:19 PM by rp0428 RSS

BULK collect for all

817257 Newbie
Currently Being Moderated
i have to update the deposit_tbl which have around 1500000 records. every day around 1 million records are to be updated.
i am using the following code :

DECLARE

l_awaiting_status number;
l_pending_status number;
CURSOR s_cur IS
SELECT DT.DEP_REF_NUM
FROM deposit_tbl dt
WHERE DT.STATUS_VALUE = l_awaiting_status
AND trunc(dt.settle_due_dt) <= trunc(SYSDATE);

TYPE fetch_array IS TABLE OF s_cur%rowTYPE;
s_array fetch_array;
BEGIN
l_awaiting_status := 16;

l_pending_status := 0;
OPEN s_cur;
LOOP
FETCH s_cur BULK COLLECT INTO s_array LIMIT 100000;

FORALL i IN 1..s_array.COUNT
UPDATE deposit_tbl
SET status_value = l_pending_status,
update_by = 'ST_SYSTEM',
update_dt = sysdate
WHERE DEP_REF_NUM = s_array (i);
EXIT WHEN s_cur%NOTFOUND;
END LOOP;
CLOSE s_cur;
END;


getting error : expression of wrong data type for condition WHERE DEP_REF_NUM = s_array (i);

How can i avoid this and update table in set of 100000 records at a time. Nonmal For loop is too costly and also parallel update is not possible.
  • 1. Re: BULK collect for all
    Paul Horth Expert
    Currently Being Moderated
    sunnymoon wrote:
    i have to update the deposit_tbl which have around 1500000 records. every day around 1 million records are to be updated.
    i am using the following code :

    DECLARE

    l_awaiting_status number;
    l_pending_status number;
    CURSOR s_cur IS
    SELECT DT.DEP_REF_NUM
    FROM deposit_tbl dt
    WHERE DT.STATUS_VALUE = l_awaiting_status
    AND trunc(dt.settle_due_dt) <= trunc(SYSDATE);

    TYPE fetch_array IS TABLE OF s_cur%rowTYPE;
    s_array fetch_array;
    BEGIN
    l_awaiting_status := 16;

    l_pending_status := 0;
    OPEN s_cur;
    LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 100000;

    FORALL i IN 1..s_array.COUNT
    UPDATE deposit_tbl
    SET status_value = l_pending_status,
    update_by = 'ST_SYSTEM',
    update_dt = sysdate
    WHERE DEP_REF_NUM = s_array (i);
    EXIT WHEN s_cur%NOTFOUND;
    END LOOP;
    CLOSE s_cur;
    END;


    getting error : expression of wrong data type for condition WHERE DEP_REF_NUM = s_array (i);

    How can i avoid this and update table in set of 100000 records at a time. Nonmal For loop is too costly and also parallel update is not possible.
    Why use a row-by-row = slow-by-slow cursor loop anyway?

    Why not do it in a single set-based update statement?

    Example:
    update deposit_tbl dt
    set status_value = 0
    , update_by = 'ST_SYSTEM'
    , update_dt = sysdate
    where dt.status_value = 16
    AND trunc(dt.settle_due_dt) <= trunc(sysdate);
    **untested**
  • 2. Re: BULK collect for all
    936481 Newbie
    Currently Being Moderated
    Hi,
    Please replace WHERE DEP_REF_NUM = s_array (i); with WHERE DEP_REF_NUM = s_array ( i).DEP_REF_NUM;.

    Thanks,
    A.K

    Edited by: Oracle Dev. on Jan 21, 2013 6:32 AM
  • 3. Re: BULK collect for all
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    You are doing it in a slow, expensive and unscalable fashion. And burning very expensive server memory while doing it. The approach is all wrong.

    The correct approach is native SQL only. And perhaps using parallel DML, or manually parallelising it via DBMS_PARALLEL_EXECUTE.
  • 4. Re: BULK collect for all
    onedbguru Pro
    Currently Being Moderated
    ++ for DBMS_PARALLEL_EXECUTE. Cool stuff and one of the most under-utilized and best kept secret tools available in 11gR2.

    There are some great examples in the documentation on how to use this feature.

    https://www.lmgtfy.com/?q=oracle+112+DBMS_PARALLEL_EXECUTE+examples

    Edited by: onedbguru on Jan 21, 2013 11:50 AM
  • 5. Re: BULK collect for all
    rp0428 Guru
    Currently Being Moderated
    Others have already said why you should just be using SQL for this.

    But assuming you needed to do this using PL/SQL you should do the update using ROWID rather than a table column. You should also modify the cursor query so it does NOT use a function on the table column. Unless you have a functional index on 'settle_due_dt' use of the TRUNC function will prevent any normal index from being used.

    And if you only have 1.5 million rows in the table how can you possibly need to update 1 million of them every day?

    1. Modify your CURSOR to fetch the ROWID of the rows that need to be updated.
    >
    CURSOR s_cur IS
    SELECT DT.ROWID rid
    FROM deposit_tbl dt
    WHERE DT.STATUS_VALUE = l_awaiting_status
    AND dt.settle_due_dt >= trunc(SYSDATE) AND dt.settle_due_dt <= SYSDATE; -- NOTE: no TRUNC on the table column
    >

    2. Modify your TYPE to be the ROWID of the cursor result
    TYPE fetch_array IS TABLE OF s_cur.rid%TYPE;
    
    3. Modify the UPDATE query to use ROWID (the cursor rid column) to do the update. ROWID is the fastest way to find the row again.
    WHERE ROWID = s_array (i);
    You should also be using a FOR UPDATE clause to lock the rows that you intend to update.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points