skip to content »

Cursor for updating records

I decided to write a couple functions that will use the dictionary and update cursor method to update tables since it is a LOT faster.

To support this method, I needed to create an index on TEST8. The biggest drawback to this method is readability. LAST UPDATE test SET fk = fk_tab(i) , fill = fill_tab(i) WHERE pk = pk_tab(i); END LOOP; CLOSE rec_cur; END; / The modern equivalent of the Updateable Join View. Parallel PL/SQL ORA-00060: deadlock detected Well, if further proof was needed that Bitmap indexes are inappropriate for tables that are maintained by multiple concurrent sessions, surely this is it.

Since Oracle does not yet provide support for record collections in FORALL, we need to use scalar collections, making for long declarations, INTO clauses, and SET clauses. Gaining in popularity due to its combination of brevity and performance, it is primarily used to INSERT and UPDATE in a single statement. Note that I have included a FIRST_ROWS hint to force an indexed nested loops plan. The Deadlock error raised by Method 8 occurred because bitmap indexes are locked at the block-level, not the row level.

I'm trying to implement best practice whenever possible.

I am following the data access tutorial published in net , the only difference is that i have an Oracle (10g) database.

Nevertheless, I tried to use its asynchronous API to develop a Google Maps application.

cursor for updating records-44

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.

cursor for updating records-20cursor for updating records-28cursor for updating records-19

The example also demonstrates how to use extended formats.I include it here because it allows us to compare the cost of context-switches to the cost of updates.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 =; END LOOP; CLOSE C1; END; / This is the simplest PL/SQL method and very common in hand-coded PL/SQL applications.Next I created another function that will accept a list of fields to update in the source table, and a list of value fields from another table (join table) that will be used to update the fields in the source table.It creates a new dictionary for each join table field and updates all input fields in the source table.The dictionary method is a very efficient way to update records (since update cursors cannot work with joined tables) and performs this operation about 100x times faster.