建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
UPDATE 算子用于更新數(shù)據(jù)表中滿足指定條件的數(shù)據(jù)行。
10年積累的做網(wǎng)站、成都網(wǎng)站制作經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶(hù)對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶(hù)得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先做網(wǎng)站后付款的網(wǎng)站建設(shè)流程，更有桂陽(yáng)免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
OceanBase 數(shù)據(jù)庫(kù)支持的 UPDATE 算子包括 UPDATE 和 MULTI PARTITION UPDATE。
UPDATE
UPDATE 算子用于更新數(shù)據(jù)表單個(gè)分區(qū)中的數(shù)據(jù)。
如下例所示，Q1 查詢(xún)更新了表 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 算子的輸出信息如下：
|
信息名稱(chēng) |
含義 |
|---|---|
|
output |
該算子輸出的表達(dá)式。 |
|
filter |
該算子上的過(guò)濾條件。由于示例中 UPDATE 算子沒(méi)有 filter，所以為 nil。對(duì)于更新語(yǔ)句，WHERE 中的謂詞會(huì)下推到基表上，比如 Q1 查詢(xún)中的 c2 = '100' 被下推到了 1 號(hào)算子上。 |
|
table_columns |
更新操作涉及的數(shù)據(jù)表的列。 |
|
update |
更新操作中所有的賦值表達(dá)式。 |
更多 UPDATE 算子的示例如下：
-
Q2 查詢(xún)更新 t1 中的所有數(shù)據(jù)行，并將 c2 的值置為 200。
-
Q3 查詢(xún)更新分區(qū)表 t2 中滿足
c1='100'的數(shù)據(jù)行，并將 c2 的值置為 150。 -
Q4 查詢(xún)更新分區(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 查詢(xún)更新表 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 查詢(xún)更新分區(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)當(dāng)前題目：創(chuàng)新互聯(lián)OceanBase教程：OceanBaseUPDATE
當(dāng)前地址：http://m.5511xx.com/article/dhdihjd.html
