The DELETE operator is used to delete data rows from a table that meet specified conditions.
OceanBase AI Database supports the DELETE and DISTRIBUTED DELETE operators.
DELETE
The DELETE operator is used to delete data from a single partition of a table.
As shown in the following example, query Q1 deletes all rows in table t1 that satisfy c2 > '100'.
obclient> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY
HASH(c1) PARTITIONS 10;
Query OK, 0 rows affected
obclient> CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT);
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';
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 of the execution plan shows the output information of the DELETE operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the DELETE operator, so the value is nil. For DELETE statements, the predicate in the WHERE clause is pushed down to the base table. For example, in query Q1, c2 > '100' is pushed down to Operator 1. |
| table_columns | The columns of the data table involved in the delete operation. |
More examples of the DELETE operator are as follows:
Query Q2 deletes all data rows from table
t1.Query Q3 deletes data rows from partitioned table
t2that satisfyc1 = 1.Query Q4 deletes data rows from partitioned table
t2that satisfyc2 > '100'. As shown in the execution plan, theDELETEoperator is placed under theEXCHANGEoperator. Therefore, Operators 2 and 3 are scheduled as one task at the partition granularity. During plan execution, Operator 4 scans the data int2that satisfiesc2 > '100', and theDELETEoperator of Operator 3 deletes only the scanned data from the corresponding partition.
Q2:
obclient> EXPLAIN DELETE FROM t1;
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;
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';
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])
DISTRIBUTED DELETE
The DISTRIBUTED DELETE operator is used to delete data from multiple partitions of a table.
As shown in the following example, query Q5 deletes all data rows in table t3 that satisfy c2 > '100'. Although t3 itself is a non-partitioned table, each data row exists in multiple partitions because a global index idx_t3_c2 exists on t3.
Q5:
obclient> EXPLAIN DELETE FROM t3 WHERE c2 > '100';
+----------------------------------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------------------------------+
| ==================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |DISTRIBUTED DELETE | |1 |26 | |
| |1 |└─PX COORDINATOR | |1 |8 | |
| |2 | └─EXCHANGE OUT DISTR |:EX10000 |1 |8 | |
| |3 | └─PX PARTITION ITERATOR| |1 |7 | |
| |4 | └─TABLE RANGE SCAN |t3(IDX_t3_c2)|1 |7 | |
| ==================================================================== |
| 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), rowset=16 |
| 2 - output([t3.c1], [t3.c2]), filter(nil), rowset=16 |
| dop=1 |
| 3 - output([t3.c1], [t3.c2]), filter(nil), rowset=16 |
| force partition granule |
| 4 - output([t3.c1], [t3.c2]), filter(nil), rowset=16 |
| access([t3.c1], [t3.c2]), partitions(p[0-2]) |
| is_index_back=false, is_global_index=true, |
| range_key([t3.c2], [t3.c1]), range(100,MAX ; MAX,MAX), |
| range_cond([t3.c2 > demote_cast('100', INT(11, 0))]) |
+----------------------------------------------------------------------------------+
23 rows in set
The outputs & filters section of the execution plan for the preceding example shows the information of the DISTRIBUTED DELETE operator. The field meanings are the same as those for the DELETE operator.