Updating a table in oracle dating agency names

Posted by / 03-Oct-2019 09:16

Update-wise, it looks as though it should perform the same as the Explicit Cursor Loop.The difference is that the Implicit Cursor internally performs bulk fetches, which should be faster than the Explicit Cursor because of the reduced context switches. I generally recommend against it for high-volume updates because the SET sub-query is nested, meaning it is performed once for each row updated. Using BULK COLLECT and FORALL statements is the new de-facto standard for PL/SQL programmers concerned about performance because it reduces context switching overheads between the PL/SQL and SQL engines.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.

Here's the function: CREATE OR REPLACE FUNCTION test_parallel_update ( test_cur IN SYS_REFCURSOR ) RETURN test_num_arr PARALLEL_ENABLE (PARTITION test_cur BY ANY) PIPELINED IS PRAGMA AUTONOMOUS_TRANSACTION; test_rec TEST%ROWTYPE; TYPE num_tab_t IS TABLE OF NUMBER(38); TYPE vc2_tab_t IS TABLE OF VARCHAR2(4000); pk_tab NUM_TAB_T; fk_tab NUM_TAB_T; fill_tab VC2_TAB_T; cnt INTEGER := 0; BEGIN LOOP FETCH test_cur BULK COLLECT INTO pk_tab, fk_tab, fill_tab LIMIT 1000; EXIT WHEN pk_tab. This is a feature of Oracle's parallel-enabled functions; they will apportion the rows of a single Ref Cursor amongst many parallel slaves, with each slave running over a different subset of the input data set. Parallel PL/SQL 28.2 27.2 6.3 Let's see how a Foreign Key constraint affects things. Although not shown here, this effect is magnified with each additional bitmap index.------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 130K| 9921K| 258K (1)| | 1 | MERGE | TEST | | | | | 2 | VIEW | | | | | | 3 | NESTED LOOPS | | 130K| 11M| 258K (1)| | 4 | TABLE ACCESS FULL | TEST2 | 128K| 6032K| 172 (5)| | 5 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 2 (0)| | 6 | INDEX UNIQUE SCAN | TEST_PK | 1 | | 1 (0)| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 109K| 8325K| 1880 (1)| | | | | 1 | PX COORDINATOR | | | | | | | | | 2 | PX SEND QC (RANDOM) | : TQ10002 | 109K| 10M| 1880 (1)| Q1,02 | P-P | HYBRID (ROW| | 9 | VIEW | | | | | Q1,00 | PCWP | | | 10 | NESTED LOOPS | | 109K| 10M| 1880 (1)| Q1,00 | PCWP | | | 11 | PX BLOCK ITERATOR | | | | | Q1,00 | PCWC | | | 12 | TABLE ACCESS FULL | TEST2 | 107K| 5062K| 2 (0)| Q1,00 | PCWP | | | 13 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 0 (0)| Q1,00 | PCWP | | | 14 | INDEX UNIQUE SCAN | TEST_PK | 1 | | 0 (0)| Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------------- This is much easier to do with Data Stage than with native PL/SQL. Parallel PL/SQL 25.3 23.8 Surely no comparison of update methods could possibly be complete without a test of Bitmap index maintenance. The other intesting outcome is the differing impact of the bitmap index on SET-based updates vs transactional updates (SQL solutions vs PL/SQL solutions).The goal is to have several separate sessions applying UPDATE statements at once, rather than using the sometimes restrictive PARALLEL DML alternative. LAST UPDATE test SET fk = fk_tab(i) , fill = fill_tab(i) WHERE pk = pk_tab(i); cnt := cnt pk_tab. In this round, I have removed the Foreign Key used in Round 2, and included a Bitmap index on TEST. PL/SQL solutions seem to incur a penalty when updating bitmap indexed tables.I noticed from the trace that PARALLEL DML used 256 parallel threads, whereas the PL/SQL method used just 128.Looking more closely at the trace files I suspect that the PARALLEL DML used 128 readers and 128 writers, although it hard to be sure.

updating a table in oracle-40updating a table in oracle-29updating a table in oracle-23

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.

One thought on “updating a table in oracle”