INSERT INTO与UNION ALL的优劣对比及性能相关疑问

场景背景 #

使用Oracle数据库，各年度数据存储在独立表中，因非DBA身份无法修改现有表结构，也无权限查看执行计划或创建视图，需将多年数据合并到新表中。测试了两种实现方案：

方案1：UNION ALL 直接建表 #

执行耗时24.169秒：

CREATE TABLE tab_union AS
SELECT * FROM tab2020
UNION ALL
SELECT * FROM tab2021
UNION ALL
SELECT * FROM tab2022;

方案2：NOLOGGING+APPEND 分批INSERT #

执行耗时60.702秒：

CREATE TABLE tab_insert NOLOGGING AS
SELECT * FROM tab2020;

INSERT /*+ APPEND */ INTO tab_insert
SELECT * FROM tab2021;

INSERT /*+ APPEND */ INTO tab_insert
SELECT * FROM tab2022;

疑问解答 #

1. 若不看重性能，是否有合理理由优先选择INSERT INTO写法？ #

有，以下几种场景下更适合用分批INSERT：

• 增量扩展方便：如果后续要逐年追加新数据（比如明年新增tab2023），直接添加一条INSERT语句即可，无需重新编写全量UNION ALL语句。
• 分步校验数据：每完成一张表的插入后，可立即核对数据行数是否正确，若某一步出现问题，无需从头执行全量操作，仅需修复对应步骤即可。
• 降低资源占用：服务器资源紧张时，分批插入能拆分数据加载压力，避免单次大查询占满CPU、IO资源，影响其他业务运行。
• 表属性调整灵活：先创建表时可单独设置分区、索引等属性，再分批插入数据，比一次性建表后再修改属性更灵活（若有对应权限）。

2. 为何UNION ALL更快？其他数据库中也是如此吗？ #

在Oracle中，UNION ALL直接建表更快的核心原因是单阶段批量写入：

• 执行CREATE TABLE AS SELECT(CTAS)时，Oracle会将所有查询结果一次性打包写入新表的数据块，全程为连续IO操作，日志量远低于多次INSERT（即使未开启NOLOGGING）。
• 而分批使用APPEND INSERT时，每次插入都是独立事务，即便使用/*+ APPEND */提示，每次都要初始化批量加载上下文，多次写入还会增加磁盘寻址开销，累加后效率远低于单次CTAS。

其他数据库的表现：

• MySQL：与Oracle类似，CREATE TABLE ... AS SELECT结合UNION ALL的方式通常比多次INSERT更快，单次批量写入的IO效率更高。
• SQL Server：SELECT ... INTO（类似CTAS）搭配UNION ALL的性能也优于多次INSERT，尤其在大数据量场景下。
• PostgreSQL：同样，CREATE TABLE AS SELECT的单阶段处理效率高于多次INSERT，即便使用COPY命令批量导入，单次全量处理也比分批更高效。

内容的提问来源于stack exchange，提问作者Thomas