OceanBase UPDATE

2021-06-29 18:13 更新

UPDATE 算子用于更新數(shù)據(jù)表中滿足指定條件的數(shù)據(jù)行。

OceanBase 數(shù)據(jù)庫(kù)支持的 UPDATE 算子包括 UPDATE 和 MULTI PARTITION UPDATE。

UPDATE

UPDATE 算子用于更新數(shù)據(jù)表單個(gè)分區(qū)中的數(shù)據(jù)。

如下例所示，Q1 查詢更新了表 t1 中所有滿足 c2 = '100' 的行，并將 c2 的值設(shè)置為 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)

上述示例中，執(zhí)行計(jì)劃展示中的 outputs & filters 詳細(xì)列出了 UPDATE 算子的輸出信息如下：

信息名稱

含義

output

該算子輸出的表達(dá)式。

filter

 該算子上的過(guò)濾條件。由于示例中 UPDATE 算子沒(méi)有 filter，所以為 nil。對(duì)于更新語(yǔ)句，WHERE 中的謂詞會(huì)下推到基表上，比如 Q1 查詢中的 c2 = '100' 被下推到了 1 號(hào)算子上。

table_columns

更新操作涉及的數(shù)據(jù)表的列。

update

更新操作中所有的賦值表達(dá)式。

更多 UPDATE 算子的示例如下：

  • Q2 查詢更新 t1 中的所有數(shù)據(jù)行，并將 c2 的值置為 200。

  • Q3 查詢更新分區(qū)表 t2 中滿足 c1='100' 的數(shù)據(jù)行，并將 c2 的值置為 150。

  • Q4 查詢更新分區(qū)表 t2 中滿足 c2 ='100' 的數(shù)據(jù)行，并將c2 的值置為rpad(t2.c2, 10, '9')。從執(zhí)行計(jì)劃中可以看到，UPDATE 算子分配在 EXCHANGE 算子下面，因此 2 號(hào)和 3 號(hào)算子會(huì)作為一個(gè) task 以分區(qū)的粒度進(jìn)行調(diào)度。執(zhí)行時(shí) 3 號(hào)算子掃描出 t2 一個(gè)分區(qū)中滿足 c2 = '100' 的數(shù)據(jù)，2 號(hào) UPDATE 算子則只會(huì)更新相應(yīng)分區(qū)下掃描出的數(shù)據(jù)。

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

MULTI PARTITION UPDATE 算子表示更新數(shù)據(jù)表多個(gè)分區(qū)中的數(shù)據(jù)。如下例所示，Q5 查詢更新表 t3 中所有滿足c2 < '100'的數(shù)據(jù)行，并將 c2 的值置為 200。雖然 t3 本身是一個(gè)非分區(qū)表，但 t3 上存在全局索引 idx_t3_c2，因此每一條數(shù)據(jù)行會(huì)存在于多個(gè)分區(qū)中。

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

更多 MULTI PARTITION UPDATE 的示例如下：

  • Q6 查詢更新分區(qū)表 t2 中滿足 c1 = 100 的數(shù)據(jù)行，并將 c1 的值設(shè)置為 101。因?yàn)楦碌牧惺侵麈I列，可能會(huì)導(dǎo)致更新后的數(shù)據(jù)行與更新前的數(shù)據(jù)行位于不同的分區(qū)，因此需要使用 MULTI PARTITION UPDATE 算子進(jìn)行更新。

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)
以上內(nèi)容是否對(duì)您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號(hào)
微信公眾號(hào)

編程獅公眾號(hào)