DELETE 算子用于删除数据表中满足指定条件的数据行。
OceanBase 数据库支持的 DELETE 算子包括 DELETE 和 MULTI PARTITION DELETE。
DELETE
DELETE 算子用于删除数据表单个分区中的数据。
如下例所示，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)上述示例中，执行计划展示中的 outputs & filters 详细列出了 DELETE 算子的输出信息如下：
信息名称 | 含义 |
|---|---|
output | 该算子输出的表达式。 |
filter | 该算子上的过滤条件。由于示例中 DELETE 算子没有设置 filter，所以为 nil。对于删除语句，WHERE 中的谓词会下推到基表上，比如 Q1 查询中的c2>'100'被下推到了 1 号算子上。 |
table_columns | 删除操作涉及的数据表的列。 |
更多 DELETE 算子的示例如下：
Q2 查询删除 t1 中的所有数据行。
Q3 查询删除分区表 t2 中满足c1 = 1的数据行。
Q4 查询删除分区表 t2 中满足c2 > '100'的数据行。从执行计划中可以看到，DELETE 算子分配在 EXCHANGE 算子下面，因此 2 号和 3 号算子会作为一个 task 以分区的粒度进行调度。在计划执行时， 3 号算子扫描出 t2 一个分区中满足c2 > '100'的数据，2 号算子 DELETE 则只会删除相应分区下扫描出的数据。
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 算子用于删除数据表多个分区中的数据。
如下例所示，Q5 查询删除了表 t3 中所有满足c2 > '100'的数据行。虽然 t3 本身是一个非分区表，但因为 t3 上存在全局索引 idx_t3_c2，因此每一条数据行会存在于多个分区中。
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])上述示例的执行计划展示中的 outputs & filters 详细列出了 MULTI PARTITION DELETE 算子的信息，字段的含义与 DELETE 算子相同。