Bulk updating multiple columns in oracle

Rated 4.71/5 based on 527 customer reviews

The results from the update_script are listed below.

In this example, the bulk operation is approximately twice the speed of the conventional update.

Notice the use of the ROW keyword in the bulk operation.

This keyword was introduced in Oracle9i Release 2 to allow updates using record definitions without needing to reference individual elements of the record, making bulk operations significantly simpler.

The returned data could be a single column, multiple columns or expressions.

When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.

This restriction means that updates and deletes which have to reference inividual columns of the collection in the where clause are still restricted to the collection-per-column approach used in Oracle8i.

The forall_test table is used to compare the performance of individual updates against bulk updates using the update_script listed below.There is an overhead associated with each context switch between the two engines.If PL/SQL code loops through a collection performing the same DML operation for each item in the collection it is possible to reduce context switches by bulk binding the whole collection to the DML statement in one operation.These data structures come in very handy when implementing algorithms that manipulate lists of program data, but they are also key elements in some of the powerful performance optimization features in PL/SQL.In this article, I will cover the two most important of these features: BULK COLLECT and FORALL.

Leave a Reply