Skip to Main Content
  • Questions

  • Update a large amount of rows in the table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 01, 2017 - 11:43 am UTC

Last updated: July 02, 2017 - 11:39 am UTC

Version: 11.0

Viewed 10K+ times! This question is

You Asked

Hi,
I have 10 million records in my table but I need to update 5 million records from that table.

I checked tom sir solutions but i didn't find a total code.That have already Create table tblname as select updations from tble and after rename old to new table.....

I need entire explanation..Please explain me here with a complete example....

I need 2 solutions at least.One is with like Tom sir sol and another one is Bulk with Forall.

and Connor said...

To *update* rows in place you:

- change each row (and hence the block that the row is on)
- potentially change indexes on affected columns
- write all of those changes to undo area because you might need to roll this back

So with every row you change, you writing a lot of redo and undo information. Fine for a few rows, but as the number of rows gets larger, it takes longer and longer and you burn more and more resources.

If you *copy* the table (or copy just the you want to keep)

- copy blocks of data (rather than rows)
- minimal undo required because if its fails you simply ignore the half-created table, your original table is unchanged
- you must create *new* indexes afterwards, and whilst this can be done efficiently, if you have dozens of them, it might take a while


There's a lot more idiosyncacies to consider, but the basic premise is simple: What is the best balance of overall execution time, resource consumption, risk that works best for you.

For example, you might go with the update option even if its the slowest and most resource hungry if you dont want to have any risk of missing a grant or an index on the new table. One option is not "better" than the other- they are *different*. You choose what works best for *your* need.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Other option : Parallel DML

Rajeshwaran, Jeyabal, July 03, 2017 - 3:26 am UTC

....
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>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.