Bulk update for million records

[sellyh19]

Hi Tim,

I have been trying to work on a solution to update a column status on my table. I have a table called customer_balance_tb with the below structure and it is populated every day with data:

Code: Select all

create table CUSTOMER_BALANCE_TB
(
  MSISDN      VARCHAR2(20) not null,
  BALANCE     NUMBER,
  DATE_N      VARCHAR2(8) not null,
  STATUS      VARCHAR2(2) default 0,
  COS         NUMBER,
  LOADDATE    DATE,
  EXPIRED_FLG VARCHAR2(20)
)

When the data is inserted in the table the status column is null as the status need to come from another table account_tb with the below structure

Code: Select all

create table account_tb
(MSISDN VARCHAR2(20) not null,
STATUS Number
)

So the number of records that can exists in the customer_balance_tb table is over a million per day. Everyday i want to update the status in the customer_balance_tb table by checking the respective customer status in the account_tb table. when i do an update with the code below it takes extremely long to update the millions of records and when the procedure completes nothing was updated. See the procedure below that i created to update status field:

Code: Select all

create or replace procedure custbal_update
is

CUR_DATE VARCHAR2(15) := TO_CHAR(SYSDATE,'DD-MON-YYYY');
  CURSOR c_status IS
    SELECT msisdn,status
    FROM   account_tb;
   
  TYPE t_num_array  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  v_msisdn      t_num_array;
  v_status t_num_array;

  v_row_count      NUMBER := 0;
BEGIN
  OPEN c_status;
  LOOP
    FETCH c_status
    BULK COLLECT INTO v_msisdn, v_status
    LIMIT 100;
    EXIT WHEN c_status%NOTFOUND;
   /* EXIT WHEN v_row_count = c_status%ROWCOUNT;
    v_row_count := c_status%ROWCOUNT;*/
   /* FOR i IN 1..v_msisdn.count LOOP
      v_status(i) := v_status(i);
    END LOOP; */
   
    FORALL i IN 1..v_msisdn.count
      UPDATE customer_balance_tb
      SET    status = v_status(i)
      WHERE  msisdn = v_msisdn(i)
      and trunc(loaddate) = CUR_DATE;
  END LOOP;
  CLOSE c_status;
  COMMIT;
END;


Kindly assist if i am missing out something here. Thanks alot for your support in advance.

Regards,
Selly

[Tim...]

Hi.

I would suggest you trace the UPDATE and see what is happening.

http://www.oracle-base.com/articles/10g ... of-10g.php

Cheers

Tim...

[sellyh19]

Hi Tim,

I can't connect to my sql plus and i am using SQL developer tool. Can you please help to see if my procedure is right.

[Tim...]

Hi.

I've told you what I think you should do!

Personally, my first suggestion would be to void PL/SQL and do the operation in a straight update. If that is not possible for some reason, then fine, go with PL/SQL.

Either way, you need to trace what you are doing to see where the time is being spent.

Cheers

Tim...

[sellyh19]

Hi Tim,

Thank you very much for your response. I managed to correct something in my procedure and the update is now running. The only problem i am faced with right now is that it takes long to update almost an hour to update 100 records. Is there any other way i can use to optimize my update to be much fater. Because there are over a million records that needs to be updated. Below is my working procedure:

Code: Select all

create or replace procedure custbal_update
is
 CURSOR c_status is
    SELECT  msisdn, status /* bulk bind */
    FROM   account_tb;

CUR_DATE VARCHAR2(15) := TO_CHAR(SYSDATE,'DD-MON-YYYY');

  TYPE t_num_array  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE t_char_array IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
  --v_subscriberid  t_num_array;
  v_msisdn      t_char_array;
  v_status t_num_array;
  v_status_usd t_num_array;
  v_row_count      NUMBER := 0;

BEGIN
  OPEN c_status;
  LOOP
    FETCH c_status
    BULK COLLECT INTO v_msisdn, v_status
    LIMIT 100;
  /*  EXIT WHEN c_status%NOTFOUND;*/
    EXIT WHEN v_row_count = c_status%ROWCOUNT;
    v_row_count := c_status%ROWCOUNT;

    FOR i IN 1..v_msisdn.count
    LOOP
      v_status_usd(i) := v_status(i);
    END LOOP;

    FORALL i IN 1..v_msisdn.count
      UPDATE customer_balance_tb
      SET    status = v_status_usd(i)
      WHERE  msisdn = v_msisdn(i)
      and trunc(loaddate) = CUR_DATE;
      COMMIT;
  END LOOP;
  CLOSE c_status;
  COMMIT;
END;


[Tim...]

Hi.

I'm trying to help you, but it doesn't appear you are actually listening to me.

Q: Do you know why it is taking do long?
A: No.

Q: How do you find out why it is taking so long?
A: You trace it.

Q: How can you make it faster?
A: Find out why it is slow (by tracing it), then identify ways to make it faster.

The first thing I suggested was not using PL/SQL and doing it with SQL. I was thinking something like this:

Code: Select all

UPDATE customer_balance_tb cb
SET    cb.status = (SELECT a.status FROM account_tb a WHERE a.msisdn = cb.msisdn)
WHERE  trunc(loaddate) = CUR_DATE;
COMMIT;

Of course, I don't know your system so I can't tell you is this is a sensible approach. Assuming it looked logically correct, how would I tell if it was sensible? I would trace it!

http://www.oracle-base.com/articles/10g ... of-10g.php

Cheers

Tim...