DELETE operators delete table rows that meet the specified criteria.
OceanBase Database supports the following types of DELETE operators: DELETE and MULTI PARTITION DELETE.
DELETE
The DELETE operator deletes data from a single partition of a table.
In the following example, query Q1 deletes all rows in Table t1 that satisfy the condition c2>'100'.
obclient> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
Query OK, 0 rows affected
obclient> CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)) PARTITION BY
HASH(c1) PARTITIONS 10;
Query OK, 0 rows affected
obclient> CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
Query OK, 0 rows affected
obclient> CREATE INDEX IDX_t3_c2 ON t3 (c2) PARTITION BY HASH(c2) PARTITIONS 3;
Query OK, 0 rows affected
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)
In the preceding example, the outputs & filters section in the execution plan display shows in detail the output information of the DELETE operator.
| Parameter | Description |
|---|---|
| output | The output expression of the operator. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the DELETE operator. For DELETE statements, the predicate in the WHERE clause is pushed down to the base table. For example, c2>'100' in Query Q1 is pushed down to the No. 1 operator. |
| table_columns | The table columns involved in the delete operation. |
More examples of the DELETE operator:
Query Q2 deletes all data rows in
t1.Query Q3 deletes data rows in a partitioned table
t2that satisfy the conditionc1 = 1.Query Q4 deletes data rows in a partitioned table
t2that satisfy the conditionc2 > '100'. As shown in the execution plan, theDELETEoperator is assigned after theEXCHANGEoperator. So, the No. 2 and No. 3 operators are scheduled as one task performed on a partition-by-partition basis. When executed, the No. 3 operator scans a partition oft2for rows satisfyingc2 > '100'. The No. 2 operatorDELETE, on the other hand, only deletes the data found by the scan from the corresponding partition.
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
The MULTI PARTITION DELETE operator deletes data from multiple partitions of a table.
In the following example, query Q5 deletes all rows that satisfy the condition c2 > '100' from Table t3. Although t3 is a non-partitioned table, it has a global index idx_t3_c2. Therefore, each data row exists in multiple partitions.
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])
In the preceding example, the outputs & filters section in the execution plan display shows in detail the output information of the MULTI PARTITION DELETE operator. The fields of the operator have the same meaning as those of the DELETE operator.