UPDATE operators update table rows that meet the specified condition.
OceanBase Database supports the following types of UPDATE operators: 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.
-- 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'\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 display shows in detail the output information of the UPDATE operator.
| Parameter | Description |
|---|---|
| output | The output expression 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 No. 1 operator. |
| 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:
Query Q2 updates all data rows in
t1and setsc2to200.Query Q3 updates data rows in Partitioned table
t2that meet thec1='100'condition and setsc2to150.Query Q4 updates data rows in partitioned table
t2that meet thec2='100'condition and setsc2torpad(t2.c2, 10, '9'). As shown in the execution plan, theUPDATEoperator is assigned under theEXCHANGEoperator. 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 oft2and exports rows that meet thec2 = '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
t2that meet thec1='100'condition and setsc1to101. 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 theMULTI PARTITION UPDATEoperator 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)