All Products
Search
Document Center

DELETE

Last Updated: Jun 18, 2021

The DELETE operator deletes table rows that meet the specified criteria.

The types of DELETE operator supported in OceanBase Database include DELETE and MULTI PARTITION DELETE.

DELETE

The DELETE operator deletes data from a single partition of a table.

In the following example, the Q1 query 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 (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)

In the preceding example, the Outputs & filters section in the demo shows in detail the output information of the DELETE operator.

Field

Description

output

The output expression of the operator.

filter

The filter conditions of the operator.In this example, the filter is set to nil because no filter condition is configured for the DELETE operator.For DELETE statements, the predicate in WHERE is pushed down to the base table. For example, c2>'100' in the Q1 query is pushed down to the No. 1 operator.

table_columns

The table columns involved in the deletion operation.

More examples of the DELETE operator:

  • Query Q2 deletes all data rows in t1.

  • Query Q3 deletes data rows in a partitioned table t2 that satisfy the condition c1 = 1.

  • Query Q4 deletes data rows in a partitioned table t2 that satisfy the condition c2 > '100'. As shown in the execution plan, the DELETE operator is assigned below the EXCHANGE operator. 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 of t2 for rows satisfying c2 > '100'. The No. 2 operator, on the other hand, only deletes from the corresponding partition the data found by the scan.

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 in table t3 that satisfy the condition c2 > '100'. Although a non-partitioned table, t3 has a global index idx_t3_c2 available. 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. Fields of the operator have the same meaning as that of the DELETE operator.