With this one, I set out to demonstrate the advantages of PARALLEL DML, didn't find what I thought I would, and ended up testing 8 different techniques to find out how they differed. The methods covered include both PL/SQL and SQL approaches.

), how I might cluster rows together that are subject to updates, and what I might do if I just get too many updates to handle. The fastest way to update every row in the table is to rebuild the table from scratch. Case 2 is common in Data Warehouses and overnight batch jobs.

I worry about how ETL tools apply updates (did you know Data Stage applys updates singly, but batches inserts in arrays? The two most common forms of Bulk Updates are: Case 1 is uninteresting.

DECLARE CURSOR c1 IS SELECT * FROM test6; rec_cur c1%rowtype; BEGIN OPEN c1; LOOP FETCH c1 INTO rec_cur; EXIT WHEN c1%notfound; UPDATE test SET fk = rec_, fill = rec_WHERE pk = rec_cur.pk; END LOOP; CLOSE C1; END; / This is the simplest PL/SQL method and very common in hand-coded PL/SQL applications.

I understand that the first step will be: CREATE TEMP_T AS SELECT * FROM T_CONTACTS.

However, there’s a trick to using this clause: You need to keep track of which rows have already been updated so that you don’t update them a second time.

The OUTPUT clause in SQL Server 2005 and later provides a way to expose which rows are affected by a Data Manipulation Language (DML) statement.

You declare a local table to hold the primary key values of the table being updated, then use the OUTPUT clause to capture the primary key values for the rows that are updated. For the WHILE loop to start, the @@ROWCOUNT function must return a value greater than 0.

As callout A in Listing 1 shows, you can trick the WHILE loop into initially executing by including a meaningless SET statement right before the WHILE condition.

The large update has to be broken down to small batches, like 10,000, at a time. It is also easy to restart in case of interruption.

WAITFOR DELAY can be included to slow down the batch processing.

Just for testing purpose what I did is, I selected table with 14 records and set a row count of 5.

This query is supposed to update records in 5, 5 and 4 but it just updates first 5 records.

I want to test on a level playing field and remove special factors that unfairly favour one method, so there are some rules: TEST (Update Source) - 100K rows TEST (Update target) - 10M rows Name Type Name Type ------------------------------ ------------ ------------------------------ ------------ PK NUMBER PK NUMBER FK NUMBER FK NUMBER FILL VARCHAR2(40) FILL VARCHAR2(40) Not many people code this way, but there are some Pro*C programmers out there who are used to Explicit Cursor Loops (OPEN, FETCH and CLOSE commands) and translate these techniques directly to PL/SQL.