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 t3into tablet1.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 tablet2. Thepartitionsparameter indicates that this value is inserted into partitionp5oft2.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 t3into a partitioned tablet2. Because the result set of the subquery cannot be determined, the data may be inserted into any one of partitionsp0top9oft2. The No. 3 operator shows thatSELECT * FROM t3is placed in a subquery namedVIEW 1. In OceanBase Database, subqueries generated by rewriting an SQL query are automatically named in the order of their generation, namelyVIEW1,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 tablet3. Although a non-partitioned table,t3has a global indexidx_t3_c2available. 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'})