Oracle updating records within same cursor Free sex chat jacksonville florida

What I love about writing SQL Tuning articles is that I very rarely end up publishing the findings I set out to achieve. We have a table containing years worth of data, most of which is static; we are updating selected rows that were recently inserted and are still volatile. For the purposes of the test, we will assume that the target table of the update is arbitrarily large, and we want to avoid things like full-scans and index rebuilds.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.

MERGE /* FIRST_ROWS*/ INTO test USING test2 new ON (= new.pk) WHEN MATCHED THEN UPDATE SET fk = , fill = ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 95331 | 7261K| 191K (1)| | 1 | MERGE | TEST | | | | | 2 | VIEW | | | | | | 3 | NESTED LOOPS | | 95331 | 8937K| 191K (1)| | 4 | TABLE ACCESS FULL | TEST2 | 95331 | 4468K| 170 (3)| | 5 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 2 (0)| | 6 | INDEX UNIQUE SCAN | TEST_PK | 1 | | 1 (0)| ------------------------------------------------------------------------------- call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 4 1 0 Execute 1 57.67 829.77 95323 383225 533245 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 57.68 829.78 95323 383229 533246 100000 Misses in library cache during parse: 1 Optimizer mode: FIRST_ROWS Parsing user id: 140 Rows Row Source Operation ------- --------------------------------------------------- 1 MERGE TEST (cr=383225 pr=95323 pw=0 time=127458586 us) 100000 VIEW (cr=371028 pr=75353 pw=0 time=619853020 us) 100000 NESTED LOOPS (cr=371028 pr=75353 pw=0 time=619653018 us) 100000 TABLE ACCESS FULL TEST2 (cr=750 pr=386 pw=0 time=505310 us) 100000 TABLE ACCESS BY INDEX ROWID TEST (cr=370278 pr=74967 pw=0 time=615942540 us) 100000 INDEX UNIQUE SCAN TEST_PK (cr=200015 pr=227 pw=0 time=4528703 us)(object id 141439) Elapsed times include waiting on following events: Event waited on Times Max.

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.

Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file scattered read 37 0.20 0.72 db file sequential read 94936 0.39 781.52 buffer exterminate 1 0.97 0.97 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.05 0.05 ********************************************************************************MERGE INTO test USING test2 new ON (= new.pk) WHEN MATCHED THEN UPDATE SET fk = , fill = --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Temp Spc| Cost (%CPU)| --------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 95331 | 7261K| | 46318 (3)| | 1 | MERGE | TEST | | | | | | 2 | VIEW | | | | | | | 3 | HASH JOIN | | 95331 | 8937K| 5592K| 46318 (3)| | 4 | TABLE ACCESS FULL| TEST2 | 95331 | 4468K| | 170 (3)| | 5 | TABLE ACCESS FULL| TEST | 10M| 458M| | 16949 (4)| --------------------------------------------------------------------------- call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.40 1 4 1 0 Execute 1 54.50 123.48 94547 82411 533095 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 54.53 123.88 94548 82415 533096 100000 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 140 Rows Row Source Operation ------- --------------------------------------------------- 1 MERGE TEST (cr=82411 pr=94547 pw=0 time=123480418 us) 100000 VIEW (cr=75424 pr=74949 pw=0 time=48081374 us) 100000 HASH JOIN (cr=75424 pr=74949 pw=0 time=47981370 us) 100000 TABLE ACCESS FULL TEST2 (cr=750 pr=335 pw=0 time=1207771 us) 9999999 TABLE ACCESS FULL TEST (cr=74674 pr=74614 pw=0 time=10033917 us) Elapsed times include waiting on following events: Event waited on Times Max.

Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 19606 0.37 41.24 db file scattered read 4720 0.52 34.20 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.03 0.03 That's a pretty significant difference: the same method (MERGE) is 6-7 times faster when performed as a Hash Join.

Search for oracle updating records within same cursor:

oracle updating records within same cursor-52oracle updating records within same cursor-67

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

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “oracle updating records within same cursor”