建站
咨詢
售后
建站咨詢
新聞中心
INSERT 算子用于將指定的數(shù)據(jù)插入數(shù)據(jù)表，數(shù)據(jù)來源包括直接指定的值和子查詢的結(jié)果。
OceanBase 數(shù)據(jù)庫支持的 INSERT 算子包括 INSERT 和 MULTI PARTITION INSERT。
INSERT
INSERT 算子用于向數(shù)據(jù)表的單個分區(qū)中插入數(shù)據(jù)。
如下例所示，Q1 查詢將值 (1, '100') 插入到非分區(qū)表 t1 中。其中 1 號算子 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'})上述示例中，執(zhí)行計劃展示中的 outputs & filters 詳細列出了 INSERT 算子的輸出信息如下：
|
信息名稱 |
含義 |
|---|---|
更多 INSERT 算子的示例如下：
Q2 查詢將值（2, '200'）、（3, '300'）插入到表 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'})Q3 查詢將子查詢
SELECT * FROM t3的結(jié)果插入到表 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)Q4 查詢將值（1, '100'）插入到分區(qū)表 t2 中，通過
partitions參數(shù)可以看出，該值會被插入到 t2 的 p5 分區(qū)。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
MULTI PARTITION INSERT 算子用于向數(shù)據(jù)表的多個分區(qū)中插入數(shù)據(jù)。
如下例所示，Q5 查詢將值（2, '200'）、（3, '300'）插入到分區(qū)表 t2 中，通過 partitions 可以看出，這些值會被插入到 t2 的 p0 和 p6 分區(qū)。
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'})上述示例的執(zhí)行計劃展示中的 outputs & filters 詳細列出了 MULTI PARTITION INSERT 算子的信息，字段的含義與 INSERT 算子相同。
更多 MULTI PARTITION INSERT 算子的示例如下：
Q6 查詢將子查詢
SELECT * FROM t3的結(jié)果插入到分區(qū)表 t2 中，因為無法確定子查詢的結(jié)果集，因此數(shù)據(jù)可能插入到 t2 的 p0 到 p9 的任何一個分區(qū)中。從1 號算子可以看到，這里的SELECT * FROM t3會被放在一個子查詢中，并將子查詢命名為 VIEW1。當(dāng) OceanBase 數(shù)據(jù)庫內(nèi)部改寫 SQL 產(chǎn)生了子查詢時，會自動為子查詢命名，并按照子查詢生成的順序命名為 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)Q7 查詢將值（1, '100'）插入到非分區(qū)表 t3 中。雖然 t3 本身是一個非分區(qū)表，但因為 t3 上存在全局索引 idx_t3_c2，因此本次插入也涉及到了多個分區(qū)。
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'})
文章題目：創(chuàng)新互聯(lián)OceanBase教程：OceanBase INSERT
本文來源：http://www.dlmjj.cn/article/djedijh.html
