All Products
Search
Document Center

UPDATE

Last Updated: Jun 18, 2021

An UPDATE operator updates table rows that meet the specified condition.

UPDATE operators supported by OceanBase Database include UPDATE and MULTI PARTITION UPDATE.

UPDATE

The UPDATE operator updates data in a single partition of a table.

In the following example, Query Q1 updates all rows in table t1 that meet the c2 = '100' condition, and sets c2 to 200.

obclient>CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)) PARTITION BY 
        HASH(c1) PARTITIONS 10;
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE INDEX IDX_t3_c2 ON t3 (c2) PARTITION BY HASH(c2) PARTITIONS 3;
Query OK, 0 rows affected (0.12 sec)

Q1: 
obclient>EXPLAIN UPDATE t1 SET c2 = '200' WHERE c2 = '100'\G;
*************************** 1. row ***************************
Query Plan:
======================================
|ID|OPERATOR            |NAME|EST. ROWS|COST  |
-----------------------------------------------
|0 |EXCHANGE IN REMOTE  |    |990      |109687|
|1 | EXCHANGE OUT REMOTE|    |990      |109687|
|2 |  UPDATE            |    |990      |109687|
|3 |   TABLE SCAN       |T1  |990      |108697|
===============================================

Outputs & filters:
-------------------------------------
  0 - output(nil), filter(nil)
  1 - output(nil), filter(nil)
  2 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}]),
      update([T1.C2=?])
  3 - output([T1.C1], [T1.C2], [?]), filter([T1.C2 = '100']),
      access([T1.C2], [T1.C1]), partitions(p0)

In the preceding example, the Outputs & filters section in the execution plan demo shows in detail the output information of the UPDATE operator.

Field

Description

output

The output expression of the operator.

filter

The filter conditions of the operator.In this example, the condition is set to nil because no filter is configured for the UPDATE operator.For UPDATE statements, the predicate in the WHERE clause is pushed down to the base table. For example, c2 = '100' in Query Q1 is pushed down to No. 1 operator.

table_columns

The columns of the source table that contain data to be updated to the destination table.

update

The assignment expressions in the update operation.

More examples of the UPDATE operator:

  • Query Q2 updates all data rows in t1 and sets c2 to 200.

  • Query Q3 updates data rows in partitioned table t2 that meet the c1='100' condition and sets c2 to 150.

  • Query Q4 updates data rows in partitioned table t2 that meet the c2='100' condition, and sets c2 to rpad(t2.c2, 10, '9'). As shown in the execution plan, the UPDATE operator is assigned under the EXCHANGE operator. Therefore, the No. 2 and No. 3 operators are scheduled as one task in a partition. During execution, the No. 3 operator scans a partition of t2, and exports rows that meet the c2 = '100' condition. The No. 2 operator, on the other hand, only updates the scanned data from the corresponding partition.

Q2: 
obclient>EXPLAIN UPDATE t1 SET c2 = '200'\G;
*************************** 1. row ***************************
Query Plan:
======================================
|ID|OPERATOR            |NAME|EST. ROWS|COST  |
-----------------------------------------------
|0 |EXCHANGE IN REMOTE  |    |100000   |161860|
|1 | EXCHANGE OUT REMOTE|    |100000   |161860|
|2 |  UPDATE            |    |100000   |161860|
|3 |   TABLE SCAN       |T1  |100000   |61860 |
===============================================

Outputs & filters:
-------------------------------------
  0 - output(nil), filter(nil)
  1 - output(nil), filter(nil)
  2 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}]),
      update([T1.C2=?])
  3 - output([T1.C1], [T1.C2], [?]), filter(nil),
      access([T1.C2], [T1.C1]), partitions(p0)

     
Q3: 
obclient>EXPLAIN UPDATE t2 SET t2.c2 = '150' WHERE t2.c1 = '100'\G;
*************************** 1. row ***************************
Query Plan:
===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |UPDATE    |    |1        |53  |
|1 | TABLE GET|T2  |1        |52  |
===================================

Outputs & filters:
-------------------------------------
  0 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]),
      update([T2.C2=?])
  1 - output([T2.C1], [T2.C2], [?]), filter(nil),
      access([T2.C1], [T2.C2]), partitions(p5)
      

Q4: 
obclient>EXPLAIN UPDATE t2 SET t2.c2 = RPAD(t2.c2, 10, '9')  WHERE t2.c2 = '100'\G;
*************************** 1. row ***************************
Query Plan:
===============================================
|ID|OPERATOR               |NAME    |EST. ROWS|COST   |
-------------------------------------------------------
|0 |PX COORDINATOR         |        |9900     |1096793|
|1 | EXCHANGE OUT DISTR    |:EX10000|9900     |1096793|
|2 |  PX PARTITION ITERATOR|        |9900     |1096793|
|3 |   UPDATE              |        |9900     |1096793|
|4 |    TABLE SCAN         |T2      |9900     |1086893|
=======================================================

Outputs & filters:
-------------------------------------
  0 - output(nil), filter(nil)
  1 - output(nil), filter(nil), dop=1
  2 - output(nil), filter(nil)
  3 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]),
      update([T2.C2=column_conv(VARCHAR,utf8mb4_bin,length:10,NULL,RPAD(T2.C2, 10, ?))])
  4 - output([T2.C1], [T2.C2], [column_conv(VARCHAR,utf8mb4_bin,length:10,NULL,RPAD(T2.C2, 10, ?))]), filter([T2.C2 = '100']),
      access([T2.C1], [T2.C2]), partitions(p[0-9])

MULTI PARTITION UPDATE

The MULTI PARTITION UPDATE operator updates data in multiple partitions of a table. In the following example, Query Q5 updates all rows in table t3 that meet the c2 < '100' condition, and sets c2 to 200. Although a non-partitioned table, t3 has a global index idx_t3_c2. Therefore, each data row exists in multiple partitions.

Q5: 
obclient>EXPLAIN UPDATE t3 SET c2 = '200' WHERE c2 < '100'\G;
*************************** 1. row ***************************
Query Plan:
========================================================
|ID|OPERATOR                |NAME         |EST. ROWS|COST |
-----------------------------------------------------------
|0 |MULTI PARTITION UPDATE  |             |10001    |27780|
|1 | PX COORDINATOR         |             |10001    |17780|
|2 |  EXCHANGE OUT DISTR    |:EX10000     |10001    |14941|
|3 |   PX PARTITION ITERATOR|             |10001    |14941|
|4 |    TABLE SCAN          |T3(IDX_T3_C2)|10001    |14941|
===========================================================

Outputs & filters:
-------------------------------------
  0 - output(nil), filter(nil), table_columns([{T3: ({T3: (T3.C1, T3.C2)}, {IDX_T3_C2: (T3.C2, T3.C1)})}]),
      update([T3.C2=?])
  1 - output([T3.C1], [T3.C2], [?]), filter(nil)
  2 - output([T3.C2], [T3.C1], [?]), filter(nil), dop=1
  3 - output([T3.C2], [T3.C1], [?]), filter(nil)
  4 - output([T3.C2], [T3.C1], [?]), filter(nil),
      access([T3.C2], [T3.C1]), partitions(p[0-2])

Another example of the MULTI PARTITION UPDATE operator:

  • Query Q6 updates data rows in partitioned table t2 that meet the c1='100' condition, and sets c1 to 101. The column to be updated is the primary key column. Therefore, updated rows may be moved to a different partition after the update. In this case, you must use the MULTI PARTITION UPDATE operator to update data.

Q6: 
obclient>EXPLAIN UPDATE t2 SET t2.c1 = 101 WHERE t2.c1 = 100\G;
*************************** 1. row ***************************
Query Plan:
===============================================
|ID|OPERATOR              |NAME|EST. ROWS|COST|
-----------------------------------------------
|0 |MULTI PARTITION UPDATE|    |1        |54  |
|1 | EXCHANGE IN DISTR    |    |1        |53  |
|2 |  EXCHANGE OUT DISTR  |    |1        |52  |
|3 |   TABLE GET          |T2  |1        |52  |
===============================================

Outputs & filters:
-------------------------------------
  0 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]),
      update([T2.C1=?])
  1 - output([T2.C1], [T2.C2], [?]), filter(nil)
  2 - output([T2.C1], [T2.C2], [?]), filter(nil)
  3 - output([T2.C1], [T2.C2], [?]), filter(nil),
      access([T2.C1], [T2.C2]), partitions(p5)