All Products
Search
Document Center

INSERT

Last Updated: Jun 18, 2021

The INSERT operator inserts specified data into a table. The data can be directly specified values or the result of a subquery.

The types of INSERT operators supported in OceanBase Database include INSERT and MULTI PARTITION INSERT.

INSERT

The INSERT operator inserts data into a single partition of a table.

In the following example, the Q1 query inserts the value (1, '100') into a non-partitioned table t1. EXPRESSION, or the No. 1 operator, generates the value for the constant expression.

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 INSERT INTO t1 VALUES (1, '100')\G;
*************************** 1. row ***************************
Query Plan:
====================================
|ID|OPERATOR   |NAME|EST. ROWS|COST|
------------------------------------
|0 |INSERT     |    |1        |1   |
|1 | EXPRESSION|    |1        |1   |
====================================

Outputs & filters:
-------------------------------------
  0 - output([__values.C1], [__values.C2]), filter(nil),
      columns([{T1: ({T1: (T1.C1, T1.C2)})}]), partitions(p0)
  1 - output([__values.C1], [__values.C2]), filter(nil)
      values({1, '100'})

In the preceding example, the Outputs & filters section in the demo shows in detail the output information of the INSERT 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 INSERT operator.

columns

The table columns involved in the insert operation.

partitions

The table partitions involved in the insert operation. A non-partitioned table is considered a single-partitioned table.

More examples of the INSERT operator:

  • Query Q2 inserts the values (2, '200') and (3, '300') into table t1.

    Q2: 
    obclient>EXPLAIN INSERT INTO t1 VALUES (2, '200'),(3, '300')\G;
    *************************** 1. row ***************************
    Query Plan:
    ====================================
    |ID|OPERATOR   |NAME|EST. ROWS|COST|
    ------------------------------------
    |0 |INSERT     |    |2        |1   |
    |1 | EXPRESSION|    |2        |1   |
    ====================================
    
    Outputs & filters:
    -------------------------------------
      0 - output([__values.C1], [__values.C2]), filter(nil),
          columns([{T1: ({T1: (T1.C1, T1.C2)})}]), partitions(p0)
      1 - output([__values.C1], [__values.C2]), filter(nil)
          values({2, '200'}, {3, '300'})
  • Query Q3 inserts the result of the subquery SELECT * FROM t3 into table t1.

    Q3: 
    obclient>EXPLAIN INSERT INTO t1 SELECT * FROM t3\G;
    *************************** 1. row ***************************
    Query Plan:
    ====================================
    |0 |INSERT              |     |100000   |117862|
    |1 | EXCHANGE IN DISTR  |     |100000   |104060|
    |2 |  EXCHANGE OUT DISTR|     |100000   |75662 |
    |3 |   SUBPLAN SCAN     |VIEW1|100000   |75662 |
    |4 |    TABLE SCAN      |T3   |100000   |61860 |
    ================================================
    
    Outputs & filters:
    -------------------------------------
      0 - output([VIEW1.C1], [VIEW1.C2]), filter(nil),
          columns([{T1: ({T1: (T1.C1, T1.C2)})}]), partitions(p0)
      1 - output([VIEW1.C1], [VIEW1.C2]), filter(nil)
      2 - output([VIEW1.C1], [VIEW1.C2]), filter(nil)
      3 - output([VIEW1.C1], [VIEW1.C2]), filter(nil),
          access([VIEW1.C1], [VIEW1.C2])
      4 - output([T3.C1], [T3.C2]), filter(nil),
          access([T3.C2], [T3.C1]), partitions(p0)
    
  • Query Q4 inserts the value (1, '100') into a partitioned table t2. The partitions parameter indicates that this value is inserted into partition p5 of t2.

    Q4: 
    obclient>EXPLAIN INSERT INTO t2 VALUES (1, '100')\G;
    *************************** 1. row ***************************
    Query Plan:
    ====================================
    |ID|OPERATOR   |NAME|EST. ROWS|COST|
    ------------------------------------
    |0 |INSERT     |    |1        |1   |
    |1 | EXPRESSION|    |1        |1   |
    ====================================
    Outputs & filters:
    -------------------------------------
      0 - output([__values.C1], [__values.C2]), filter(nil),
          columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p5)
      1 - output([__values.C1], [__values.C2]), filter(nil)
          values({1, '100'})

MULTI PARTITION INSERT

The MULTI PARTITION INSERT operator inserts data into multiple partitions of a table.

In the following example, query Q5 inserts the values ( 2, '200') and (3, '300') into a partitioned table t2. The partitions parameter indicates that these values are inserted into partitions p0 and p6 of t2.

Q5: 
obclient>EXPLAIN INSERT INTO t2 VALUES (2, '200'),(3, '300')\G;
*************************** 1. row ***************************
Query Plan:
===============================================
|ID|OPERATOR              |NAME|EST. ROWS|COST|
-----------------------------------------------
|0 |MULTI PARTITION INSERT|    |2        |1   |
|1 | EXPRESSION           |    |2        |1   |
===============================================

Outputs & filters:
-------------------------------------
  0 - output([__values.C1], [__values.C2]), filter(nil),
      columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p0, p6)
  1 - output([__values.C1], [__values.C2]), filter(nil)
      values({2, '200'}, {3, '300'})

In the preceding example, the Outputs & filters section in the execution plan display shows in detail the output information of the MULTI PARTITION INSERT operator. Fields of the operator have the same meaning as that of the INSERT operator.

More examples of the MULTI PARTITION INSERT operator:

  • Query Q6 inserts the result of the subquery SELECT * FROM t3 into a partitioned table t2. Because the result set of the subquery cannot be determined, the data may be inserted into any one of partitions p0 to p9 of t2. The No. 1 operator shows that SELECT * FROM t3 is placed in a subquery named VIEW 1. In OceanBase Database, subqueries generated by rewriting an SQL query are automatically named in the order of their generation, namely VIEW1, VIEW2, VIEW3...

    Q6: 
    obclient>EXPLAIN INSERT INTO t2 SELECT * FROM t3\G;
    *************************** 1. row ***************************
    Query Plan:
    ==============================================
    |ID|OPERATOR             |NAME|EST. ROWS|COST|
    --------------------------------------------------
    |0 |MULTI PARTITION INSERT|     |100000   |117862|
    |1 | EXCHANGE IN DISTR    |     |100000   |104060|
    |2 |  EXCHANGE OUT DISTR  |     |100000   |75662 |
    |3 |   SUBPLAN SCAN       |VIEW1|100000   |75662 |
    |4 |    TABLE SCAN        |T3   |100000   |61860 |
    ==================================================
    
    Outputs & filters:
    -------------------------------------
      0 - output([VIEW1.C1], [VIEW1.C2]), filter(nil),
          columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p[0-9])
      1 - output([VIEW1.C1], [VIEW1.C2]), filter(nil)
      2 - output([VIEW1.C1], [VIEW1.C2]), filter(nil)
      3 - output([VIEW1.C1], [VIEW1.C2]), filter(nil),
          access([VIEW1.C1], [VIEW1.C2])
      4 - output([T3.C1], [T3.C2]), filter(nil),
          access([T3.C2], [T3.C1]), partitions(p0)
  • Query Q7 inserts the value (1, '100') into a non-partitioned table t3. Although a non-partitioned table, t3 has a global index idx_t3_c2 available. Therefore, the insert operation involves multiple partitions.

    Q7: 
    obclient>EXPLAIN INSERT INTO t3 VALUES (1, '100')\G;
    *************************** 1. row ***************************
    Query Plan:
    ==============================================
    |ID|OPERATOR              |NAME|EST. ROWS|COST|
    -----------------------------------------------
    |0 |MULTI PARTITION INSERT|    |1        |1   |
    |1 | EXPRESSION           |    |1        |1   |
    ===============================================
    
    Outputs & filters:
    -------------------------------------
      0 - output([__values.C1], [__values.C2]), filter(nil),
          columns([{T3: ({T3: (T3.C1, T3.C2)}, {IDX_T3_C2: (T3.C2, T3.C1)})}]), partitions(p0)
      1 - output([__values.C1], [__values.C2]), filter(nil)
          values({1, '100'})