UPDATE 算子用于更新数据表中满足指定条件的数据行。

OceanBase 数据库支持的 UPDATE 算子包括 UPDATE 和 MULTI PARTITION UPDATE。

UPDATE

UPDATE 算子用于更新数据表单个分区中的数据。

如下例所示，Q1 查询更新了表 t1 中所有满足 c2 = '100' 的行，并将 c2 的值设置为 200。

  1. obclient>CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
  2. Query OK, 0 rows affected (0.12 sec)
  4. obclient>CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)) PARTITION BY 
  5.         HASH(c1) PARTITIONS 10;
  6. Query OK, 0 rows affected (0.12 sec)
  8. obclient>CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
  9. Query OK, 0 rows affected (0.12 sec)
  11. obclient>CREATE INDEX IDX_t3_c2 ON t3 (c2) PARTITION BY HASH(c2) PARTITIONS 3;
  12. Query OK, 0 rows affected (0.12 sec)
  14. Q1: 
  15. obclient>EXPLAIN UPDATE t1 SET c2 = '200' WHERE c2 = '100'\G;
  16. *************************** 1. row ***************************
  17. Query Plan:
  18. ======================================
  19. |ID|OPERATOR            |NAME|EST. ROWS|COST  |
  20. -----------------------------------------------
  21. |0 |EXCHANGE IN REMOTE  |    |990      |109687|
  22. |1 | EXCHANGE OUT REMOTE|    |990      |109687|
  23. |2 |  UPDATE            |    |990      |109687|
  24. |3 |   TABLE SCAN       |T1  |990      |108697|
  25. ===============================================
  27. Outputs & filters:
  28. -------------------------------------
  29.   0 - output(nil), filter(nil)
  30.   1 - output(nil), filter(nil)
  31.   2 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}]),
  32.       update([T1.C2=?])
  33.   3 - output([T1.C1], [T1.C2], [?]), filter([T1.C2 = '100']),
  34.       access([T1.C2], [T1.C1]), partitions(p0)

上述示例中，执行计划展示中的 outputs & filters 详细列出了 UPDATE 算子的输出信息如下：

信息名称

含义

output

该算子输出的表达式。

filter

该算子上的过滤条件。由于示例中 UPDATE 算子没有 filter，所以为 nil。对于更新语句，WHERE 中的谓词会下推到基表上，比如 Q1 查询中的 c2 = ‘100’ 被下推到了 1 号算子上。

table_columns

更新操作涉及的数据表的列。

update

更新操作中所有的赋值表达式。

更多 UPDATE 算子的示例如下：

  • Q2 查询更新 t1 中的所有数据行，并将 c2 的值置为 200。

  • Q3 查询更新分区表 t2 中满足 c1='100' 的数据行，并将 c2 的值置为 150。

  • Q4 查询更新分区表 t2 中满足 c2 ='100' 的数据行，并将c2 的值置为rpad(t2.c2, 10, '9')。从执行计划中可以看到，UPDATE 算子分配在 EXCHANGE 算子下面，因此 2 号和 3 号算子会作为一个 task 以分区的粒度进行调度。执行时 3 号算子扫描出 t2 一个分区中满足 c2 = '100' 的数据，2 号 UPDATE 算子则只会更新相应分区下扫描出的数据。

  1. Q2: 
  2. obclient>EXPLAIN UPDATE t1 SET c2 = '200'\G;
  3. *************************** 1. row ***************************
  4. Query Plan:
  5. ======================================
  6. |ID|OPERATOR            |NAME|EST. ROWS|COST  |
  7. -----------------------------------------------
  8. |0 |EXCHANGE IN REMOTE  |    |100000   |161860|
  9. |1 | EXCHANGE OUT REMOTE|    |100000   |161860|
  10. |2 |  UPDATE            |    |100000   |161860|
  11. |3 |   TABLE SCAN       |T1  |100000   |61860 |
  12. ===============================================
  14. Outputs & filters:
  15. -------------------------------------
  16.   0 - output(nil), filter(nil)
  17.   1 - output(nil), filter(nil)
  18.   2 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}]),
  19.       update([T1.C2=?])
  20.   3 - output([T1.C1], [T1.C2], [?]), filter(nil),
  21.       access([T1.C2], [T1.C1]), partitions(p0)
  24. Q3: 
  25. obclient>EXPLAIN UPDATE t2 SET t2.c2 = '150' WHERE t2.c1 = '100'\G;
  26. *************************** 1. row ***************************
  27. Query Plan:
  28. ===================================
  29. |ID|OPERATOR  |NAME|EST. ROWS|COST|
  30. -----------------------------------
  31. |0 |UPDATE    |    |1        |53  |
  32. |1 | TABLE GET|T2  |1        |52  |
  33. ===================================
  35. Outputs & filters:
  36. -------------------------------------
  37.   0 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]),
  38.       update([T2.C2=?])
  39.   1 - output([T2.C1], [T2.C2], [?]), filter(nil),
  40.       access([T2.C1], [T2.C2]), partitions(p5)
  43. Q4: 
  44. obclient>EXPLAIN UPDATE t2 SET t2.c2 = RPAD(t2.c2, 10, '9')  WHERE t2.c2 = '100'\G;
  45. *************************** 1. row ***************************
  46. Query Plan:
  47. ===============================================
  48. |ID|OPERATOR               |NAME    |EST. ROWS|COST   |
  49. -------------------------------------------------------
  50. |0 |PX COORDINATOR         |        |9900     |1096793|
  51. |1 | EXCHANGE OUT DISTR    |:EX10000|9900     |1096793|
  52. |2 |  PX PARTITION ITERATOR|        |9900     |1096793|
  53. |3 |   UPDATE              |        |9900     |1096793|
  54. |4 |    TABLE SCAN         |T2      |9900     |1086893|
  55. =======================================================
  57. Outputs & filters:
  58. -------------------------------------
  59.   0 - output(nil), filter(nil)
  60.   1 - output(nil), filter(nil), dop=1
  61.   2 - output(nil), filter(nil)
  62.   3 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]),
  63.       update([T2.C2=column_conv(VARCHAR,utf8mb4_bin,length:10,NULL,RPAD(T2.C2, 10, ?))])
  64.   4 - output([T2.C1], [T2.C2], [column_conv(VARCHAR,utf8mb4_bin,length:10,NULL,RPAD(T2.C2, 10, ?))]), filter([T2.C2 = '100']),
  65.       access([T2.C1], [T2.C2]), partitions(p[0-9])

MULTI PARTITION UPDATE

MULTI PARTITION UPDATE 算子表示更新数据表多个分区中的数据。如下例所示，Q5 查询更新表 t3 中所有满足c2 < '100'的数据行，并将 c2 的值置为 200。虽然 t3 本身是一个非分区表，但 t3 上存在全局索引 idx_t3_c2，因此每一条数据行会存在于多个分区中。

  1. Q5: 
  2. obclient>EXPLAIN UPDATE t3 SET c2 = '200' WHERE c2 < '100'\G;
  3. *************************** 1. row ***************************
  4. Query Plan:
  5. ========================================================
  6. |ID|OPERATOR                |NAME         |EST. ROWS|COST |
  7. -----------------------------------------------------------
  8. |0 |MULTI PARTITION UPDATE  |             |10001    |27780|
  9. |1 | PX COORDINATOR         |             |10001    |17780|
  10. |2 |  EXCHANGE OUT DISTR    |:EX10000     |10001    |14941|
  11. |3 |   PX PARTITION ITERATOR|             |10001    |14941|
  12. |4 |    TABLE SCAN          |T3(IDX_T3_C2)|10001    |14941|
  13. ===========================================================
  15. Outputs & filters:
  16. -------------------------------------
  17.   0 - output(nil), filter(nil), table_columns([{T3: ({T3: (T3.C1, T3.C2)}, {IDX_T3_C2: (T3.C2, T3.C1)})}]),
  18.       update([T3.C2=?])
  19.   1 - output([T3.C1], [T3.C2], [?]), filter(nil)
  20.   2 - output([T3.C2], [T3.C1], [?]), filter(nil), dop=1
  21.   3 - output([T3.C2], [T3.C1], [?]), filter(nil)
  22.   4 - output([T3.C2], [T3.C1], [?]), filter(nil),
  23.       access([T3.C2], [T3.C1]), partitions(p[0-2])

更多 MULTI PARTITION UPDATE 的示例如下：

  • Q6 查询更新分区表 t2 中满足 c1 = 100 的数据行，并将 c1 的值设置为 101。因为更新的列是主键列，可能会导致更新后的数据行与更新前的数据行位于不同的分区，因此需要使用 MULTI PARTITION UPDATE 算子进行更新。
  1. Q6: 
  2. obclient>EXPLAIN UPDATE t2 SET t2.c1 = 101 WHERE t2.c1 = 100\G;
  3. *************************** 1. row ***************************
  4. Query Plan:
  5. ===============================================
  6. |ID|OPERATOR              |NAME|EST. ROWS|COST|
  7. -----------------------------------------------
  8. |0 |MULTI PARTITION UPDATE|    |1        |54  |
  9. |1 | EXCHANGE IN DISTR    |    |1        |53  |
  10. |2 |  EXCHANGE OUT DISTR  |    |1        |52  |
  11. |3 |   TABLE GET          |T2  |1        |52  |
  12. ===============================================
  14. Outputs & filters:
  15. -------------------------------------
  16.   0 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]),
  17.       update([T2.C1=?])
  18.   1 - output([T2.C1], [T2.C2], [?]), filter(nil)
  19.   2 - output([T2.C1], [T2.C2], [?]), filter(nil)
  20.   3 - output([T2.C1], [T2.C2], [?]), filter(nil),
  21.       access([T2.C1], [T2.C2]), partitions(p5)