The UPDATE operator is used to update data rows in a data table that meet specified conditions.
OceanBase AI Database supports the UPDATE and DISTRIBUTED UPDATE operators.
UPDATE
The UPDATE operator is used to update data in a single partition of a data table.
As shown in the following example, query Q1 updates all rows in table t1 that satisfy c2 = '100' and sets the value of column c2 to 200.
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 of the execution plan shows the output information of the UPDATE operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the UPDATE operator, so the value is nil. For UPDATE statements, the predicate in the WHERE clause is pushed down to the base table. For example, in query Q1, c2 = '100' is pushed down to Operator 3. |
| table_columns | The columns of the data table involved in the update operation. |
| update | The assignment expressions in the update operation. |
More examples of the UPDATE operator are as follows:
Query Q2 updates all data rows in table
t1and sets the value of columnc2to 200.Q2:
obclient> EXPLAIN UPDATE t1 SET c2 = '200';The result is as follows:
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)Query Q3 updates data rows in partitioned table
t2that satisfyc1='100'and sets the value of columnc2to 150.Q3:
obclient> EXPLAIN UPDATE t2 SET t2.c2 = '150' WHERE t2.c1 = '100';The result is as follows:
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)Query Q4 updates data rows in partitioned table
t2that satisfyc2 = '100'and sets the value of columnc2torpad(t2.c2, 10, '9'). As shown in the execution plan, theUPDATEoperator is placed under theEXCHANGEoperator. Therefore, Operators 2 and 3 are scheduled as one task at the partition granularity. During execution, Operator 4 scans the data that satisfiesc2 = '100'within a partition oft2, and Operator 3, theUPDATEoperator, updates only the scanned data within the corresponding partition.Q4:
obclient> EXPLAIN UPDATE t2 SET t2.c2 = RPAD(t2.c2, 10, '9') WHERE t2.c2 = '100';The result is as follows:
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 is used to update data in multiple partitions of a data table. As shown in the following example, query Q5 updates all data rows in table t3 that satisfy c2 < '100' and sets the value of column c2 to 200. Although t3 itself is a non-partitioned table, it has a global index idx_t3_c2, so each data row exists in multiple partitions.
Q5:
obclient> EXPLAIN UPDATE t3 SET c2 = '200' WHERE c2 < '100';
The result is as follows:
+--------------------------------------------------------------------------------------------------------+
| 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 DISTRIBUTED UPDATE are as follows:
Query Q6 updates data rows in partitioned table
t2that satisfyc1 = 100and sets the value of columnc1to 101. Since the updated column is the primary key column, the updated data rows may be in different partitions from the original data rows. Therefore, theDISTRIBUTED UPDATEoperator is required for the update.Q6:
obclient> EXPLAIN UPDATE t2 SET t2.c1 = 101 WHERE t2.c1 = 100;The result is as follows:
+------------------------------------------------------------------+ | 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]) | +------------------------------------------------------------------+