DELETE 算子用于删除数据表中满足指定条件的数据行。

OceanBase 数据库支持的 DELETE 算子包括 DELETE 和 MULTI PARTITION DELETE。

DELETE

DELETE 算子用于删除数据表单个分区中的数据。

如下例所示，Q1 查询删除了表 t1 中所有满足 c2>'100' 的行。

  1. obclient>CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
  2. Query OK, 0 rows affected (0.12 sec)
  4. obclient>CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)) PARTITION BY 
  5.              HASH(c1) PARTITIONS 10;
  6. Query OK, 0 rows affected (0.12 sec)
  8. obclient>CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
  9. Query OK, 0 rows affected (0.12 sec)
  11. obclient>CREATE INDEX IDX_t3_c2 ON t3 (c2) PARTITION BY HASH(c2) PARTITIONS 3;
  12. Query OK, 0 rows affected (0.12 sec)
  14. Q1: 
  15. obclient>EXPLAIN DELETE FROM t1 WHERE c2 > '100'\G;
  16. *************************** 1. row ***************************
  17. Query Plan:
  18. ======================================
  19. |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  20. --------------------------------------
  21. |0 |DELETE     |    |10000    |118697|
  22. |1 | TABLE SCAN|T1  |10000    |108697|
  23. ======================================
  25. Outputs & filters:
  26. -------------------------------------
  27.   0 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}])
  28.   1 - output([T1.C1], [T1.C2]), filter([T1.C2 > '100']),
  29.       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 则只会删除相应分区下扫描出的数据。

  1. Q2: 
  2. obclient>EXPLAIN DELETE FROM t1\G;
  3. *************************** 1. row ***************************
  4. Query Plan:
  5. ======================================
  6. |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  7. --------------------------------------
  8. |0 |DELETE     |    |100000   |161860|
  9. |1 | TABLE SCAN|T1  |100000   |61860 |
  10. ======================================
  12. Outputs & filters:
  13. -------------------------------------
  14.   0 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}])
  15.   1 - output([T1.C1], [T1.C2]), filter(nil),
  16.       access([T1.C1], [T1.C2]), partitions(p0)
  19. Q3: 
  20. obclient>EXPLAIN DELETE FROM t2 WHERE c1 = 1\G;
  21. *************************** 1. row ***************************
  22. Query Plan:
  23. ===================================
  24. |ID|OPERATOR  |NAME|EST. ROWS|COST|
  25. -----------------------------------
  26. |0 |DELETE    |    |1        |53  |
  27. |1 | TABLE GET|T2  |1        |52  |
  28. ===================================
  30. Outputs & filters:
  31. -------------------------------------
  32.   0 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}])
  33.   1 - output([T2.C1], [T2.C2]), filter(nil),
  34.       access([T2.C1], [T2.C2]), partitions(p5) 
  37. Q4: 
  38. obclient>EXPLAIN DELETE FROM t2 WHERE c2 > '100'\G;
  39. *************************** 1. row ***************************
  40. Query Plan:
  41. ===============================================
  43. |ID|OPERATOR               |NAME    |EST. ROWS|COST   |
  44. -------------------------------------------------------
  45. |0 |PX COORDINATOR         |        |100000   |1186893|
  46. |1 | EXCHANGE OUT DISTR    |:EX10000|100000   |1186893|
  47. |2 |  PX PARTITION ITERATOR|        |100000   |1186893|
  48. |3 |   DELETE              |        |100000   |1186893|
  49. |4 |    TABLE SCAN         |T2      |100000   |1086893|
  50. ==================================================
  52. Outputs & filters:
  53. -------------------------------------
  54.   0 - output(nil), filter(nil)
  55.   1 - output(nil), filter(nil), dop=1
  56.   2 - output(nil), filter(nil)
  57.   3 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}])
  58.   4 - output([T2.C1], [T2.C2]), filter([T2.C2 > '100']),
  59.       access([T2.C1], [T2.C2]), partitions(p[0-9])

MULTI PARTITION DELETE

MULTI PARTITION DELETE 算子用于删除数据表多个分区中的数据。

如下例所示，Q5 查询删除了表 t3 中所有满足 c2 > '100' 的数据行。虽然 t3 本身是一个非分区表，但因为 t3 上存在全局索引 idx_t3_c2，因此每一条数据行会存在于多个分区中。

  1. Q5: 
  2. obclient>EXPLAIN DELETE FROM t3 WHERE c2 > '100'\G;
  3. *************************** 1. row ***************************
  4. Query Plan:
  5. ========================================================
  7. |ID|OPERATOR                |NAME         |EST. ROWS|COST |
  8. -----------------------------------------------------------
  9. |0 |MULTI PARTITION DELETE  |             |10001    |27780|
  10. |1 | PX COORDINATOR         |             |10001    |17780|
  11. |2 |  EXCHANGE OUT DISTR    |:EX10000     |10001    |14941|
  12. |3 |   PX PARTITION ITERATOR|             |10001    |14941|
  13. |4 |    TABLE SCAN          |T3(IDX_T3_C2)|10001    |14941|
  14. ===========================================================
  16. Outputs & filters:
  17. -------------------------------------
  18.   0 - output(nil), filter(nil), table_columns([{T3: ({T3: (T3.C1, T3.C2)}, {IDX_T3_C2: (T3.C2, T3.C1)})}])
  19.   1 - output([T3.C1], [T3.C2]), filter(nil)
  20.   2 - output([T3.C2], [T3.C1]), filter(nil), dop=1
  21.   3 - output([T3.C2], [T3.C1]), filter(nil)
  22.   4 - output([T3.C2], [T3.C1]), filter(nil),
  23.       access([T3.C2], [T3.C1]), partitions(p[0-2])

上述示例的执行计划展示中的 outputs & filters 详细列出了 MULTI PARTITION DELETE 算子的信息，字段的含义与 DELETE 算子相同。