OceanBase DELETE

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)

更多 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ū)的粒度進行調(diào)度。在計劃執(zhí)行時， 3 號算子掃描出 t2 一個分區(qū)中滿足 c2 > '100' 的數(shù)據(jù)，2 號算子 DELETE 則只會刪除相應分區(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])

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