INSERT

INSERT operators insert specified data into a table. The data can be specified values or the result of a subquery.

OceanBase Database supports the following types of INSERT operators: INSERT and DISTRIBUTED INSERT.

INSERT

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

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

-- In Oracle mode:

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

-- In MySQL mode:

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 INSERT INTO t1 VALUES (1, '100');

Query Plan:
====================================
|ID|OPERATOR   |NAME|EST. ROWS|COST|
------------------------------------
|0 |INSERT     |    |1        |1   |
|1 | EXPRESSION|    |1        |1   |
====================================

Outputs & filters:
-------------------------------------
  0 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,__values.C1)], [column_conv(VARCHAR2,utf8mb4_bin,length:10,NULL,__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 shows in detail the output information of the INSERT operator.

More examples of the INSERT operator are as follows:

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

  Q2:
  obclient> EXPLAIN INSERT INTO t1 VALUES (2, '200'),(3, '300');
  
  Query Plan:
  ====================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST|
  ------------------------------------
  |0 |INSERT     |    |2        |1   |
  |1 | EXPRESSION|    |2        |1   |
  ====================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,__values.C1)], [column_conv(VARCHAR2,utf8mb4_bin,length:10,NULL,__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;
  
  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');
  
  Query Plan:
  ====================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST|
  ------------------------------------
  |0 |INSERT     |    |1        |1   |
  |1 | EXPRESSION|    |1        |1   |
  ====================================
  Outputs & filters:
  -------------------------------------
    0 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,__values.C1)], [column_conv(VARCHAR2,utf8mb4_bin,length:10,NULL,__values.C2)]), filter(nil),
        columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p5)
    1 - output([__values.C1], [__values.C2]), filter(nil)
        values({1, '100'})
  

DISTRIBUTED INSERT

The DISTRIBUTED 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');

Query Plan:
===============================================
|ID|OPERATOR              |NAME|EST. ROWS|COST|
-----------------------------------------------
|0 |DISTRIBUTED INSERT|    |2        |1   |
|1 | EXPRESSION           |    |2        |1   |
===============================================

Outputs & filters:
-------------------------------------
  0 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,__values.C1)], [column_conv(VARCHAR2,utf8mb4_bin,length:10,NULL,__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 shows in detail the output information of the DISTRIBUTED INSERT operator. The fields of the operator have the same meaning as those of the INSERT operator.

More examples of the DISTRIBUTED INSERT operator are as follows:

• Query Q6 inserts the result of the subquery SELECT * FROM t3 into a partitioned table t2. The result set of the subquery cannot be determined, so the data may be inserted into any one of the partitions from p0 to p9 of t2. Operator 3 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, such as VIEW1, VIEW2, and VIEW3.

  Q6:
  obclient> EXPLAIN INSERT INTO t2 SELECT * FROM t3;
  
  Query Plan:
  ==============================================
  |ID|OPERATOR          |NAME |EST. ROWS|COST |
   ---------------------------------------------
  |0 |DISTRIBUTED INSERT|     |100000   |41687|
  |1 | SUBPLAN SCAN     |VIEW1|100000   |40184|
  |2 |  TABLE SCAN      |T3   |100000   |38681|
   =============================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(VARCHAR2,utf8mb4_bin,length:10,NULL,VIEW1.C2)]), filter(nil),
      columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p[0-9])
    1 - output([VIEW1.C1], [VIEW1.C2]), filter(nil),
      access([VIEW1.C1], [VIEW1.C2])
    2 - 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 t3 is a non-partitioned table, it has a global index idx_t3_c2. Therefore, the insert operation involves multiple partitions.

  Q7:
  obclient> EXPLAIN INSERT INTO t3 VALUES (1, '100');
  
  Query Plan:
  ===========================================
  |ID|OPERATOR          |NAME|EST. ROWS|COST|
  -------------------------------------------
  |0 |DISTRIBUTED INSERT|    |1        |20  |
  |1 | EXPRESSION       |    |1        |1   |
  ===========================================
  
  Outputs & filters:
  -------------------------------------
   0 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,__values.C1)], [column_conv(VARCHAR2,utf8mb4_bin,length:10,NULL,__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'})