Hello All,
Can anyone explain how the Parallel DML -Especially Parallel UPDATE works in the below scenario ?
UPDATE
(SELECT /*+ parallel(F 8) parallel(S 8) */ COL_KEY
FROM FACT_TABLE PARTITION (PART_01) F,
STAGE_TABLE S
WHERE F.JOIN_KEY = S.JOIN_KEY
AND F.EVENT_KEY between x and y ) b
SET t.COL_KEY = t.COL_KEY - 1 ;
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | | | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 8971K| | | Q1,04 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | FACT_TABLE | | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 8971K| | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 8971K| | | Q1,03 | P->P | RANGE |
| 6 | UPDATE | FACT_TABLE | | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 8971K| | | Q1,03 | PCWP | |
| 8 | PX SEND HASH (BLOCK ADDRESS)| :TQ10002 | 8971K| | | Q1,02 | P->P | HASH (BLOCK|
| 9 | HASH JOIN BUFFERED | | 8971K| | | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 4853K| | | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 4853K| | | Q1,00 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 4853K| | | Q1,00 | PCWC | |
| 13 | TABLE ACCESS FULL | STAGE_TABLE | 4853K| | | Q1,00 | PCWP | |
| 14 | PX RECEIVE | | 8971K| | | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10001 | 8971K| | | Q1,01 | P->P | HASH |
| 16 | PX BLOCK ITERATOR | | 8971K| KEY | KEY | Q1,01 | PCWC | |
| 17 | TABLE ACCESS FULL | FACT_TABLE | 8971K| 2612 | 2612 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------
My question is about the Operation ID - 3 which is INDEX MAINTENNACE
the FACT_TABLE has total of 15 local partitioned indexes, that includes index on the COL_KEY.
Does the parallel Update will have to update all the 15 indexes ? or it just need to update the index defined on the column "COL_KEY" ?
As per my understanding parallel reads will be selecting blocks to PGA, so changes to blocks in PGA causes oracle to invalidate all the indexes on the interested partition ? Does it requires maintenance/rebuild of the all the indexes ?
Documentation on this subject is not very clear, all the documents talks about the parallel direct path insert which requires maintenance for all indexes.
Appreciate your comments.