You Asked

Hi Tom,

i have a scenario where i need to update so many tables(around 80) at once and each table having a minimum of 5 Lakhs records and i used below approach.
it is taking more time(don't know exactly because it is still running from more than one day).

can you suggest any other approach or how can i do the below update in an effective manner?

CURSOR C_TABLES IS
      SELECT TABLE_NAME
        FROM ALL_TABLES
       WHERE TABLE_NAME LIKE '%Condtion%';

FOR REC_TAB IN C_TABLES LOOP
    
      V_SQL_STRING := 'SELECT TABLE_NAME.C1,
                       TABLE_NAME.C2,
                TABLE_NAME.C3,
                NVL(IRS.C4,''UNDEFINED'') SM
           FROM ' || REC_TAB.TABLE_NAME ||
                      ' TABLE_NAME, T2 IRS
          WHERE TABLE_NAME.C1= T2.C1
            AND TABLE_NAME.C2 BETWEEN IRS.C2 AND IRS.C3';
    
      OPEN Cursor FOR V_SQL_STRING;
    
      LOOP
        FETCH V_Cursor
          INTO V_C1, V_C2, V_C3, V_C4;
        EXIT WHEN V_Cursor%NOTFOUND;
      
        V_SQL_STRING := ' UPDATE ' || REC_TAB.TABLE_NAME ||
                        ' SET C3= ''' || V_C4|| '''
           WHERE C1= ''' || V_C1|| '''
           AND C2= ''' || V_C2|| '''
           AND C3= ''' || C_C3|| ''' ';
      
        EXECUTE IMMEDIATE V_SQL_STRING;
        ---DBMS_OUTPUT.PUT_LINE('Updated Table:' || REC_TAB.TABLE_NAME);
        COMMIT;
      END LOOP;    
END LOOP;

Thanks in Advance..
Srinadh Tummala

and Connor said...

There are a *lot* of improvements we could make - but lets start with 2 easy ones, and see if that gets enough boost for you.

1) bind variables

Change

V_SQL_STRING := ' UPDATE ' || REC_TAB.TABLE_NAME ||
                        ' SET C3= ''' || V_C4|| '''
           WHERE C1= ''' || V_C1|| '''
           AND C2= ''' || V_C2|| '''
           AND C3= ''' || C_C3|| ''' ';
      
        EXECUTE IMMEDIATE V_SQL_STRING;

V_SQL_STRING := ' UPDATE ' || REC_TAB.TABLE_NAME ||
                        ' SET C3= :1 '||
           ' WHERE C1= :2'||
           ' AND C2= :3 '||
           ' AND C3= :4 ';
      
        EXECUTE IMMEDIATE V_SQL_STRING using v_c1, v_c2, v_c3, v_c4;

2) less commits

Change

COMMIT;
      END LOOP;    
END LOOP;

  END LOOP;    
  COMMIT;
END LOOP;

If its still too slow, get back to us and we'll have more for you.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

it is very useful but as it is daily job please provide some more tips to improve the performance

Srinadh Tummala, April 07, 2017 - 10:19 am UTC

Many thanks for the quick answer.

also need more tips to tune this as it is a daily job and also tables get increased.

Thanks,
Srinadh

April 08, 2017 - 5:16 am UTC

Let's see some code and execution timings for

1) before you made the changes (ie original version)
2) our suggested changes

so we can see the scope of current improvements

Tested for one table and it has 144423 records

Srinadh Tummala, April 07, 2017 - 12:23 pm UTC

It's running for more than 1 and 1/2 half hour and it goes on.

could you please suggest any other approach for bulk update.

OPEN V_cursor FOR V_SQL_STRING;

V_SQL_STRING := ' UPDATE ' || REC_TAB.TABLE_NAME ||
' SET C4= :1
WHERE C1= :2
AND C2= :3
AND C3= :4 ';

LOOP

FETCH v_CURSOR BULK COLLECT
INTO DATA_COLL LIMIT 10000;
EXIT WHEN V_CURSOR%NOTFOUND;

FORALL I IN 1..DATA_COLL.COUNT

EXECUTE IMMEDIATE V_SQL_STRING
USING DATA_COLL(I).C1,
DATA_COLL(I).C2,
DATA_COLL(I).C3,
DATA_COLL(I).C4;

END LOOP;
COMMIT;
END LOOP;

Thanks in Advance
Srinadh

To Srinadh

J. Laurindo Chiappa, April 07, 2017 - 3:48 pm UTC

We all know that some overhead is unavoidable in dynamic SQL ** and ** in the use of LOOPs : please mensure the performance of direct, non-dynamic SQL for UPDATE of just one table againts the same routine updating dynamically the only the same table , more or less in this way (I am using NUMBER as the datatype, change it if not correct) :

SQL> VARIABLE V1 number;
SQL> VARIABLE V2 number;
SQL> VARIABLE V3 number;
SQL> VARIABLE V4 number;

SQL> execute :v1 := valuedesiredfor v1;
SQL> execute :v2 := anothervaluedesiredforv2;
SQL> execute :v3 := anothervaluedesiredforv3;
SQL> execute :v4 := anothervaluedesiredforv4;

and then :

SQL> UPDATE nameofthetable SET C4= :V1 WHERE C1= :V2 AND C2= :V3 AND C3= :V4;
SQL> COMMIT;

Compare performance of the code against the dynamic SQL code passing as argument the same table used in the code above, and see what you see : IF, as I suspect, the performance of the non-dynamic AND the dynamic code is more or less the same, you will look in other sources (such as index mainteinance, lock waiting, among many others) the root cause of the bad performance....
If this is not the case and the non-dynamic code is much quicker, you coud generate the 80 fixed UPDATEs in a script, maybe...

Best regards,

J. Laurindo Chiappa

Regards,

J, Laurindo Chiappa

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

it is very useful but as it is daily job please provide some more tips to improve the performance

Tested for one table and it has 144423 records

To Srinadh

More to Explore

DBMS_OUTPUT

How to update tables in loop having 5L records in each table effectively in less time

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

it is very useful but as it is daily job please provide some more tips to improve the performance

Tested for one table and it has 144423 records

To Srinadh

More to Explore

DBMS_OUTPUT