标签
PostgreSQL , Oracle , 多表批量写入 , insert all into , CTE
背景
Oracle支持 insert all 的语法，同时往多个表插入。
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2125362
PostgreSQL可以通过CTE语法达到同样的效果。
https://www.postgresql.org/docs/11/static/queries-with.html
Oracle insert all into 例子
create table a(id int, c1 int, c2 int);
create table b(id int, c1 int, c2 int);
set autotrace on;
insert all
into a (id,c1) values (id, col1)
into b (id,c2) values (id, col2)
select rownum as id, trunc(dbms_random.value(0, 100)) as col1, trunc(dbms_random.value(0, 100)) as col2 from dual connect by level <=10000;
20000 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 702343910
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | MULTI-TABLE INSERT | | | | | |
| 2 | INTO | A | | | | |
| 3 | INTO | B | | | | |
| 4 | VIEW | | 1 | 39 | 2 (0)| 00:00:01 |
| 5 | COUNT | | | | | |
|* 6 | CONNECT BY WITHOUT FILTERING| | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(LEVEL<=10000)
Statistics
----------------------------------------------------------
531 recursive calls
661 db block gets
205 consistent gets
0 physical reads
392344 redo size
823 bytes sent via SQL*Net to client
932 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
20000 rows processed
PostgreSQL CTE例子
create table a(id int, c1 int, c2 int);
create table b(id int, c1 int, c2 int);
insert all
into a (id,c1) values (id, col1)
into b (id,c2) values (id, col2)
select id, random() col1, random() col2 from generate_series(1,10000) t(id);
ERROR: 42601: syntax error at or near "all"
LINE 1: insert all
^
LOCATION: scanner_yyerror, scan.l:1087
with tmp as (select id, random() col1, random() col2 from generate_series(1,10000) t(id)),
ins1 as (insert into a (id,c1) select id,col1 from tmp)
insert into b (id,c2) select id,col2 from tmp;
INSERT 0 10000
postgres=# select count(*) from a;
count
-------
10000
(1 row)
postgres=# select count(*) from b;
count
-------
10000
(1 row)
postgres=# select * from a limit 10;
id | c1 | c2
----+----+----
1 | 0 |
2 | 1 |
3 | 1 |
4 | 0 |
5 | 1 |
6 | 0 |
7 | 1 |
8 | 0 |
9 | 0 |
10 | 0 |
(10 rows)
postgres=# select * from b limit 10;
id | c1 | c2
----+----+----
1 | | 0
2 | | 0
3 | | 1
4 | | 1
5 | | 0
6 | | 1
7 | | 1
8 | | 1
9 | | 0
10 | | 0
(10 rows) 