update tuning
I am trying to tune the following SQL
update TABLE A set (set col1, col2, col3) =
(select col1, col2 col3 from TABLE b where a.colA = b.colA and a.colB = b.colB and b.col10=X);
TABLE has about 20 mil rows. COLA and COLb are first two columns in the primary key on the table. Here is my explain plan
| 0 | UPDATE STATEMENT | | 5934K| 786M| 563M (20)|999:59:59 | | | |
| 1 | UPDATE | TABLE| | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 5934K| 786M| 45978 (1)| 00:09:12 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 5934K| 786M| 45978 (1)| 00:09:12 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS STORAGE FULL| TABLE| 5934K| 786M| 45978 (1)| 00:09:12 | Q1,00 | PCWP | |
| 6 | TABLE ACCESS BY INDEX ROWID | TABLE | 1 | 139 | 4 (0)| 00:00:01 | | | |