Oracle sql updating multiple rows

Although the number of physical disk blocks and Current Mode Gets are about the same in each test, the Hash Join method performs multi-block reads, resulting in fewer visits to the disk.

All 8 methods above were benchmarked on the assumption that the target table is arbitrarily large and the subset of rows/blocks to be updated are relatively small.

Here is the statement that calls the Parallel Enabled Table Function: Note that we are using a SELECT statement to call a function that performs an UPDATE. You need to make the function an AUTONOMOUS TRANSACTION to stop it from throwing an error. For this round, I have created a parent table and a Foreign Key on the FK column. Given that most bitmap-indexed tables would have several such indexes (as bitmap indexes are designed to be of most use in combination), this shows that PL/SQL is virtually non-viable as a means of updating a large number of rows.

But just bear with me, it is the closest PL/SQL equivalent I can make to a third-party ETL Tool such as Data Stage with native parallelism. For brevity, this time we'll just flush the buffer cache and run about 5 minutes worth of indexed reads to cycle the disk cache. Although we are updating only 1% of the rows in the table, those rows are almost perfectly distributed throughout the table.

I spend an inordinate proportion of design time of an ETL system worrying about the relative proportion of rows inserted vs updated.

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.

Update-wise, it looks as though it should perform the same as the Explicit Cursor Loop.