UPDATE|V4.3.1| docs|Distributed Database

UPDATE

Last Updated：2024-06-28 05:30:31 Updated

UPDATE operators update table rows that meet the specified condition.

OceanBase Database supports the following types of UPDATE operators: UPDATE and DISTRIBUTED 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.

-- In Oracle mode:

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

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

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

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

-- In MySQL mode:

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

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

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

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

Q1:
obclient> EXPLAIN UPDATE t1 SET c2 = '200' WHERE c2 = '100';

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 shows in detail the output information of the UPDATE operator.

Field	Description
output	The output expressions of the operator.
filter	The filter conditions of the operator. In this example, `filter` is set to `nil` because no filter condition 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 Operator 1.
table_columns	The columns of the source table that contain data to be updated.
update	The assignment expressions in the update operation.

More examples of the UPDATE operator are as follows:

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 operators 2 and 3 are scheduled as one task in a partition. During execution, Operator 3 scans a partition of t2 and exports rows that meet the c2 = '100' condition. Operator 2, on the other hand, only updates the scanned data from the corresponding partition.

Q2:
obclient> EXPLAIN UPDATE t1 SET c2 = '200';

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';

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';

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])

DISTRIBUTED UPDATE

The DISTRIBUTED 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';
+--------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                             |
+--------------------------------------------------------------------------------------------------------+
| =======================================================================                                |
| |ID|OPERATOR                      |NAME         |EST.ROWS|EST.TIME(us)|                                |
| -----------------------------------------------------------------------                                |
| |0 |DISTRIBUTED UPDATE            |             |1       |61          |                                |
| |1 |└─DISTRIBUTED TABLE RANGE SCAN|T3(IDX_T3_C2)|1       |6           |                                |
| =======================================================================                                |
| 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=column_conv(VARCHAR2,utf8mb4_bin,length:10,NULL,cast('200', VARCHAR2(1048576 )))]) |
|   1 - output([T3.C1], [T3.C2]), filter(nil), rowset=256                                                |
|       access([T3.C1], [T3.C2]), partitions(p[0-2])                                                     |
|       is_index_back=false, is_global_index=true,                                                       |
|       range_key([T3.C2], [T3.C1]), range(MIN,MIN ; 100,MIN),                                           |
|       range_cond([T3.C2 < cast('100', VARCHAR2(1048576 ))])                                            |
+--------------------------------------------------------------------------------------------------------+

More examples of the DISTRIBUTED UPDATE operator are as follows:

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 DISTRIBUTED UPDATE operator for update.

Q6:
obclient> EXPLAIN UPDATE t2 SET t2.c1 = 101 WHERE t2.c1 = 100;
+------------------------------------------------------------------+
| Query Plan                                                       |
+------------------------------------------------------------------+
| ==================================================               |
| |ID|OPERATOR          |NAME|EST.ROWS|EST.TIME(us)|               |
| --------------------------------------------------               |
| |0 |DISTRIBUTED UPDATE|    |1       |34          |               |
| |1 |└─TABLE GET       |T2  |1       |3           |               |
| ==================================================               |
| Outputs & filters:                                               |
| -------------------------------------                            |
|   0 - output(nil), filter(nil)                                   |
|       table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]),             |
|       update([T2.C1=column_conv(NUMBER,PS:(-1,0),NOT NULL,101)]) |
|   1 - output([T2.C1], [T2.C2]), filter(nil), rowset=256          |
|       access([T2.C1], [T2.C2]), partitions(p5)                   |
|       is_index_back=false, is_global_index=false,                |
|       range_key([T2.C1]), range[100 ; 100],                      |
|       range_cond([T2.C1 = 100])                                  |
+------------------------------------------------------------------+

Previous topic

DELETE

Last

Next topic

MERGE