....
I have 10 million records in my table but I need to update 5 million records from that table
.....When updated, how does this newly updated value different from the existing values? does they increase in length? do they lead to row migration/block chaining?
with no row migration/block chaining and no indexes on the updated columns - Parallel DML would be the other option to consider for evaluation.
The below test case updates 5M rows in less than couple of minutes.
demo@PDB1> alter session enable parallel dml ;
Session altered.
demo@PDB1>
demo@PDB1> explain plan for
2 update /*+ parallel(big_table,4) */ big_table
3 set object_name = lower(object_name) ,
4 object_type = lower(object_type)
5 where rownum <= 5000000 ;
Explained.
demo@PDB1>
demo@PDB1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3073966858
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5000K| 228M| 14079 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 10M| 457M| 14079 (1)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | UPDATE | BIG_TABLE | | | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 10M| 457M| 14079 (1)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH (BLOCK ADDRESS)| :TQ10001 | 10M| 457M| 14079 (1)| 00:00:01 | Q1,01 | S->P | HASH (BLOCK|
| 6 | BUFFER SORT | | 5000K| 228M| | | Q1,01 | SCWP | |
|* 7 | COUNT STOPKEY | | | | | | Q1,01 | SCWP | |
| 8 | PX RECEIVE | | 10M| 457M| 14079 (1)| 00:00:01 | Q1,01 | SCWP | |
| 9 | PX SEND 1 SLAVE | :TQ10000 | 10M| 457M| 14079 (1)| 00:00:01 | Q1,00 | P->S | 1 SLAVE |
|* 10 | COUNT STOPKEY | | | | | | Q1,00 | PCWC | |
| 11 | PX BLOCK ITERATOR | | 10M| 457M| 14079 (1)| 00:00:01 | Q1,00 | PCWC | |
| 12 | TABLE ACCESS FULL | BIG_TABLE | 10M| 457M| 14079 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter(ROWNUM<=5000000)
10 - filter(ROWNUM<=5000000)
Note
-----
- Degree of Parallelism is 2 because of table property
29 rows selected.
demo@PDB1> set timing on
demo@PDB1> update /*+ parallel(big_table,4) */ big_table
2 set object_name = lower(object_name) ,
3 object_type = lower(object_type)
4 where rownum <= 5000000 ;
5000000 rows updated.
Elapsed: 00:01:25.81
demo@PDB1> commit;
Commit complete.
Elapsed: 00:00:00.00
demo@PDB1>