CODEWITHSUNDEEP
sqloracle-databaseperformanceplsqlsql-update
Imran Hemani
Imran Hemani

Reputation: 629

Oracle SQL optimal update

I have the data in two tables:

**Supplier:** ERPSupplier, RMSSupplier

**ItemLoc:** Item, Location, Supplier

The Supplier in ItemLoc is the ERPSupplier from the Supplier table. I need to substitute RMSSupplier after comparing with ERPSupplier.

What is the optimal way of doing the update? There are 10 million records in ItemLoc Table.

Currently I am doing by the PlSQL Block, but its taking too much time:

DECLARE
  cursor c1 is
    select * from Supplier;

BEGIN

  FOR r in c1 LOOP

    update mig_item_loc
       set Supplier = r.RMSSupplier
       where Supplier = r.ERPSupplier;

  END LOOP;

END;

Upvotes: 0

Views: 72

Answers (2)

Vance
Vance

Reputation: 897

@ziesemer is correct on that. If you want to make it faster then you want to consider using bulk collect. The concept seems hard to grasp at first but here's a sample application of bulk collect in your code:

  DECLARE
    cursor c1 is
      select * from Supplier;
   type  RMSSupplier_type is table of Supplier.RMSSupplier%type index by pls_integer;
   type ERPSupplier_type is table of Supplier.ERPSupplier%type index by pls_integer;
   tableOfRMSSupplier RMSSupplier_type
   tableOfERPSupplier ERPSupplier_type;
  BEGIN
     select  RMSSupplier, ERPSupplier BULK COLLECT INTO  tableOfRMSSupplier, tableOfERPSupplier FROM Supplier;
     FORALL a in 1..tableOfRMSSupplier.COUNT
        update mig_item_loc
            set Supplier = tableOfRMSSupplier(a)
            where Supplier = tableOfERPSupplier(a);              
  END;

You can also try this single line update:

 update mig_item_loc a 
 set a.Supplier = (select b.RMSSupplier from Supplier b where a.Supplier=b.ERPSupplier)

Upvotes: 3

ziesemer
ziesemer

Reputation: 28697

Depending upon the version of the Oracle database you are using, you may get some advantages out of using a BULK COLLECT (https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1203923200346667188).

I'm also thinking that you should be able to accomplish this without PL/SQL at all. https://dba.stackexchange.com/questions/3033/how-to-update-a-table-from-a-another-table has a few considerations in this regard.

Upvotes: 2

Related Questions