In my previous article i have given the idea about how to update the records in sql. In this article i would like to give the detailed idea about the way to update bulk of records in Oracle Database with multiple examples.I would like to give the examples of update bulk of records in oracle one one table named Customer and other table named Company.
What are different ways to update bulk of records in oracle database?
There are various methodologies which we are using to update bulk of records in oracle database. I would like to discuss the following ways in detail with real life industry examples. These examples will give you clear idea about update process of oracle in detail.
Assumptions :
We are assuming that there are two tables – Customer information – Which is the name of customer visiting to specified E commerce website. There are millions of records in customer table but we are assuming that there are 999999 records in customer table. There is another table named Customer_Entry which also has 777777 records. The primary key named Customer_ID is in both tables.
Types of updates :
1.Update using For loop
2.Traditional update (Updating records individually)
3.Bulk update using BULK COLLECT and FOR ALL statement
4.DIRECT UPDATE SQL
5.MERGE Statement to updates record fast
6.Update using INLINE view method.
We need to execute all the ways and need to check the e performance of the different update statements.
Scenario 1 : Traditional way to update individual records and commit after for loop
begin
for cusrsor_1 in (select * from customer)
loop
update customer_entry
set
customer_no = cursor_1.customer_no, customer_name=cursor_1.customer_name, customer_product=c.customer_product where
customer_id= c.customer_id;
end loop; commit;
end;
/PL/SQL procedure successfully completed.
Elapsed: 00:02:42.36
If you can check the first example where we are trying to update all records using for loop and then use commit statement. If you can execute the specific PL SQL block. It is taking around 2 mins and 42 seconds to complete the procedure and to update the records.
Scenario 2 : Traditional way to update individual records and commit inside for loop
begin
for cusrsor_1 in (select * from customer)
loop
update customer_entry
set
customer_no = cursor_1.customer_no, customer_name=cursor_1.customer_name, customer_product=c.customer_product where
customer_id= c.customer_id;
commit;
end loop;
end;
/PL/SQL procedure successfully completed.
Elapsed: 00:03:58.39
If we can check the process and timing to execute the records or update the records in database it is approximately 3 mins and 58 seconds. Here loop will run commit statement after every record update so the time to update the record is higher than single commit record.
Scenario 3 : BULK UPDATE using BULK COLLECT and FOR ALL
declare
type customer_t is table of Customer%rowtype index by
pls_integer; l_customer_data customer_t;
begin Select *
bulk collect into l_customer_data from Customer;
forall rec in 1 .. l_customer_data.count
update customer_entry e set e.customer_no= l_customer_data(rec).customer_no,
e.customer_name= l_employee_data(rec).customer_name,
e.customer_product= l_employee_data(rec).customer_product
where r.customer_id= l_employee_data(rec).customer_id;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:53.55
The bulk collect method is faster method than traditional methods. We require to create a type variable where we can fetch all customer data and we need to use bulk collect statement in forall clause so that the records in the forall clause or specified table will be updated correctly. If we can see the Elapsed time it is around 54 seconds which is more faster than other methods.
4. DIRECT UPDATE SQL
begin
update customer_entry ent
set (customer_no, customer_name, customer_product) =
( select customer_no, customer_name, customer_product from customer c where c.customer_id= ent.customer_id )
where c.customer_id in (select customer_id from customer);
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:58.22
The direct update will take 58 seconds to update all the records. if we can compare that with bulk collect it took 3-4 seconds extra to update all records
Scenario 5 : MERGE STATEMENT – Fastest way to update bulk of records
begin
merge into customer_entry er using
customer c
on (er.customer_id= c.customer_id)
when matched then
update set er.customer_number= c.customer_number,
er.cutomer_name= c.customer_name,
er.customer_product= c.customer_product;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:36.23
The fastest way to update the bulk of records is using the Merge statement. The merge statement took 36 seconds to update records in fast way.
Scenario 6 :UPDATE using INLINE View Method
begin
update
(
select r.customer_id, r.customer_number, r.customer_name, r.customer_product,
e.customer_number cnum, e.customer_name cname, e.customer_product cprod
from customer_entry r, customer e
where r.customer_id= e.customer_id
) 9
set customer_number = cnum
customer_name= cname,
customer_product= cprod;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:37.97
With using the inline view method the bulk update will complete approximately in 38 seconds which is also the fast way like Merge statement.
What we learn?
The thinking of every SQL or PLSQL developer is BULK COLLECT and FOR ALL statement is better for doing bulk operations. If we can observe the details in above 6 queries the Update SQL statement would be giving the better performance than Bulk collect and for all. So try to use SQL code in PL/SQL API to do fast update. But it will vary scenario to scenario so you can always check the scenarios. If you want fastest performance try to write merge statement or Inline view.
These are multiple methods which are used to update the bulk of records in oracle database.I hope it is useful article and helpful to all PL/SQL developers.If you like this article or if you have any concerns with the same kindly comment it in comments section.