建站
咨詢
售后
建站咨詢
新聞中心
DELETE 算子用于刪除數(shù)據(jù)表中滿足指定條件的數(shù)據(jù)行。
我們是從2013年成立的成都網(wǎng)站建設(shè)公司,提供網(wǎng)站建設(shè),電商網(wǎng)站設(shè)計開發(fā),外貿(mào)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)頁設(shè)計,微信平臺小程序開發(fā)、等服務(wù)。為客戶創(chuàng)造有價值的品牌營銷體驗，讓互聯(lián)網(wǎng)提升企業(yè)的競爭力！
OceanBase 數(shù)據(jù)庫支持的 DELETE 算子包括 DELETE 和 MULTI PARTITION DELETE。
DELETE
DELETE 算子用于刪除數(shù)據(jù)表單個分區(qū)中的數(shù)據(jù)。
如下例所示，Q1 查詢刪除了表 t1 中所有滿足 c2>'100' 的行。
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 DELETE FROM t1 WHERE c2 > '100'\G;
*************************** 1. row ***************************
Query Plan:
======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |DELETE | |10000 |118697|
|1 | TABLE SCAN|T1 |10000 |108697|
======================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}])
1 - output([T1.C1], [T1.C2]), filter([T1.C2 > '100']),
access([T1.C1], [T1.C2]), partitions(p0)上述示例中，執(zhí)行計劃展示中的 outputs & filters 詳細(xì)列出了 DELETE 算子的輸出信息如下：
|
信息名稱 |
含義 |
|---|---|
output | 該算子輸出的表達(dá)式。 |
filter | 該算子上的過濾條件。由于示例中 DELETE 算子沒有設(shè)置 filter，所以為 nil。對于刪除語句，WHERE 中的謂詞會下推到基表上，比如 Q1 查詢中的 c2>'100' 被下推到了 1 號算子上。 |
table_columns | 刪除操作涉及的數(shù)據(jù)表的列。 |
更多 DELETE 算子的示例如下：
-
Q2 查詢刪除 t1 中的所有數(shù)據(jù)行。
-
Q3 查詢刪除分區(qū)表 t2 中滿足
c1 = 1的數(shù)據(jù)行。 -
Q4 查詢刪除分區(qū)表 t2 中滿足
c2 > '100'的數(shù)據(jù)行。從執(zhí)行計劃中可以看到，DELETE 算子分配在 EXCHANGE 算子下面，因此 2 號和 3 號算子會作為一個 task 以分區(qū)的粒度進(jìn)行調(diào)度。在計劃執(zhí)行時， 3 號算子掃描出 t2 一個分區(qū)中滿足c2 > '100'的數(shù)據(jù)，2 號算子 DELETE 則只會刪除相應(yīng)分區(qū)下掃描出的數(shù)據(jù)。
Q2:
obclient>EXPLAIN DELETE FROM t1\G;
*************************** 1. row ***************************
Query Plan:
======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |DELETE | |100000 |161860|
|1 | TABLE SCAN|T1 |100000 |61860 |
======================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}])
1 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
Q3:
obclient>EXPLAIN DELETE FROM t2 WHERE c1 = 1\G;
*************************** 1. row ***************************
Query Plan:
===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |DELETE | |1 |53 |
|1 | TABLE GET|T2 |1 |52 |
===================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}])
1 - output([T2.C1], [T2.C2]), filter(nil),
access([T2.C1], [T2.C2]), partitions(p5)
Q4:
obclient>EXPLAIN DELETE FROM t2 WHERE c2 > '100'\G;
*************************** 1. row ***************************
Query Plan:
===============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------
|0 |PX COORDINATOR | |100000 |1186893|
|1 | EXCHANGE OUT DISTR |:EX10000|100000 |1186893|
|2 | PX PARTITION ITERATOR| |100000 |1186893|
|3 | DELETE | |100000 |1186893|
|4 | TABLE SCAN |T2 |100000 |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)})}])
4 - output([T2.C1], [T2.C2]), filter([T2.C2 > '100']),
access([T2.C1], [T2.C2]), partitions(p[0-9])MULTI PARTITION DELETE
MULTI PARTITION DELETE 算子用于刪除數(shù)據(jù)表多個分區(qū)中的數(shù)據(jù)。
如下例所示，Q5 查詢刪除了表 t3 中所有滿足 c2 > '100' 的數(shù)據(jù)行。雖然 t3 本身是一個非分區(qū)表，但因為 t3 上存在全局索引 idx_t3_c2，因此每一條數(shù)據(jù)行會存在于多個分區(qū)中。
Q5:
obclient>EXPLAIN DELETE FROM t3 WHERE c2 > '100'\G;
*************************** 1. row ***************************
Query Plan:
========================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-----------------------------------------------------------
|0 |MULTI PARTITION DELETE | |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)})}])
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])上述示例的執(zhí)行計劃展示中的 outputs & filters 詳細(xì)列出了 MULTI PARTITION DELETE 算子的信息，字段的含義與 DELETE 算子相同。
網(wǎng)頁名稱：創(chuàng)新互聯(lián)OceanBase教程：OceanBaseDELETE
地址分享：http://m.fisionsoft.com.cn/article/coijpce.html
