Updating multiple rows in oracle black dating online panther

Rated 4.62/5 based on 891 customer reviews

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.

It's a bit of a kludge, but we can do this in PL/SQL using a Parallel Enable Table Function. COUNT; END LOOP; CLOSE test_cur; COMMIT; PIPE ROW(cnt); RETURN; END; / Note that it receives its data via a Ref Cursor parameter. FK RUN 1 RUN 2 ----------------------------------- ----- ----- 1. A single bitmap index has added around 10% to the overall runtime of PL/SQL solutions, whereas the set-based (SQL-based) solutions run faster than the B-Tree indexes case (above).

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 sequential read 1 0.02 0.02 reliable message 1 0.00 0.00 enq: RO - fast object reuse 1 0.00 0.00 os thread startup 256 0.09 23.61 PX Deq: Join ACK 7 0.00 0.00 PX Deq: Parse Reply 15 0.09 0.19 PX Deq Credit: send blkd 35 0.00 0.00 PX qref latch 5 0.00 0.00 PX Deq: Execute Reply 1141 1.96 30.30 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.05 0.05 We can see here that the Parallel Co-ordinator spent 23.61 seconds (of the 57.94 elapsed) simply starting up the parallel threads, and 30.3 seconds waiting for them to do their stuff.And here are the wait events for just ONE of the parallel threads from the same test case: Elapsed times include waiting on following events: Event waited on Times Max.If the proportion of updated blocks increases, then the average cost of finding those rows decreases; the exercise becomes one of tuning the data access rather than tuning the update.Why is the Parallel PL/SQL (Method 8) approach much faster than the Parallel DML MERGE (Method 7)? Below we see the trace from the Parallel Coordinator session of Method 7: MERGE /* first_rows */ INTO test USING test5 new ON (= new.pk) WHEN MATCHED THEN UPDATE SET fk = , fill = call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.02 0 4 1 0 Execute 1 1.85 57.91 1 7 2 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 1.87 57.94 1 11 3 100000 Misses in library cache during parse: 1 Optimizer mode: FIRST_ROWS Parsing user id: 140 Rows Row Source Operation ------- --------------------------------------------------- 128 PX COORDINATOR (cr=7 pr=1 pw=0 time=57912088 us) 0 PX SEND QC (RANDOM) : TQ10002 (cr=0 pr=0 pw=0 time=0 us) 0 INDEX MAINTENANCE TEST (cr=0 pr=0 pw=0 time=0 us)(object id 0) 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us) 0 PX SEND RANGE : TQ10001 (cr=0 pr=0 pw=0 time=0 us) 0 MERGE TEST (cr=0 pr=0 pw=0 time=0 us) 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us) 0 PX SEND HYBRID (ROWID PKEY) : TQ10000 (cr=0 pr=0 pw=0 time=0 us) 0 VIEW (cr=0 pr=0 pw=0 time=0 us) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL TEST5 (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID TEST (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN TEST_PK (cr=0 pr=0 pw=0 time=0 us)(object id 141439) Elapsed times include waiting on following events: Event waited on Times Max.

Leave a Reply