The INSERT operator is used to insert specified data into a table. The data source can be values specified directly or the results of subqueries.
OceanBase AI Database supports two forms of the INSERT operator: regular INSERT and DISTRIBUTED INSERT.
Regular INSERT
Regular INSERT is used to insert data into a single partition of a table.
As shown in the following example, query Q1 inserts the row (1, '100') into the non-partitioned table t1. Operator 1, EXPRESSION, is used to generate the value of a constant expression.
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 of the execution plan shows the output information of the INSERT 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 INSERT operator, so the value is nil. |
| columns | The columns of the data table involved in the insert operation. |
| partitions | The partitions of the data table involved in the insert operation. A non-partitioned table can be considered a partitioned table with only one partition. |
More examples of the INSERT operator are as follows:
Query Q2 inserts the rows (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 t3into tablet1.Q3: obclient> EXPLAIN INSERT INTO t1 SELECT * FROM t3; Query Plan: ================================================ |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------ |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 row (1, '100') into the partitioned table
t2. Thepartitionsparameter indicates that this value will be inserted into partitionp5oft2.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
DISTRIBUTED INSERT is used to insert data into multiple partitions of a table.
As shown in the following example, query Q5 inserts the rows (2, '200') and (3, '300') into the partitioned table t2. The partitions parameter indicates that these values will be 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'})
The outputs & filters section of the execution plan for the preceding example shows the information of the DISTRIBUTED INSERT operator. The field meanings are the same as those for the INSERT operator.
More examples of the DISTRIBUTED INSERT operator are as follows:
Query Q6 inserts the result of the subquery
SELECT * FROM t3into the partitioned tablet2. Because the result set of the subquery cannot be determined, the data may be inserted into any partition fromp0top9oft2. As shown for Operator 3,SELECT * FROM t3is placed in a subquery namedVIEW1. When OceanBase AI Database internally rewrites SQL and generates subqueries, it automatically names themVIEW1,VIEW2,VIEW3, and so on.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 row (1, '100') into the non-partitioned table
t3. Althought3itself is a non-partitioned table, this insert operation also involves multiple partitions because the table has a global indexidx_t3_c2.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'})