5 Replies Latest reply: Jan 21, 2013 2:19 PM by rp0428 RSS

    BULK collect for all

    817257
      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
          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
            Herndon
            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
              Billy~Verreynne
              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
                ++ 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
                  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.