如何高效向Oracle数据库插入100万条数据？

你原来用UNION拼接单条SELECT的方式效率极低（UNION会做去重排序，每条SELECT单独执行），要插入100万条数据，推荐以下几种高效方案：

方案一：用CONNECT BY生成规律化批量数据（最快最简洁） #

如果你的REASON_ID、TAXCODE可以按规律生成（比如随机值、自增后缀），直接用CONNECT BY一次性生成所有数据，配合APPEND提示走直接路径插入：

-- 先调整序列缓存，减少IO开销（可选但推荐）
ALTER SEQUENCE ID_SEQ CACHE 10000;

-- 批量插入100万条数据
INSERT /*+ APPEND */ INTO ID_NO_APPROVAL_TEST(ID, REASON_ID, TAXCODE, STATUS, CREATE_DATE, USER_CREATE)
SELECT ID_SEQ.NEXTVAL,
       MOD(ROWNUM, 10) + 1, -- 生成1-10的REASON_ID，按需修改范围
       'TAX_' || LPAD(ROWNUM, 9, '0'), -- 生成唯一TAXCODE，比如TAX_000000001到TAX_100000000
       1,
       SYSDATE,
       'abcxyz'
FROM DUAL
CONNECT BY ROWNUM <= 1000000;

COMMIT;

为什么快： #

• CONNECT BY是Oracle原生的批量数据生成方式，一次生成100万行，避免多次SQL执行的开销
• /*+ APPEND */提示让数据库跳过缓冲区，直接写入数据文件，大幅提升写入速度（注意：此时表会被独占锁定，插入期间其他会话无法修改该表）

方案二：临时表+批量插入（适合无规律数据） #

如果你的REASON_ID、TAXCODE是无规律的特定值集合，先把这些数据导入临时表，再批量插入目标表：

1. 创建临时表存储待插入的业务数据：

CREATE GLOBAL TEMPORARY TABLE TMP_ID_NO_DATA (
    REASON_ID NUMBER(10),
    TAXCODE VARCHAR2(30),
    USER_CREATE VARCHAR2(100)
) ON COMMIT PRESERVE ROWS; -- 提交后保留数据，按需选择

2. 将所有需要的业务数据导入临时表（可以用SQL*Loader、外部表或批量INSERT导入）

3. 从临时表批量插入目标表：

INSERT /*+ APPEND */ INTO ID_NO_APPROVAL_TEST(ID, REASON_ID, TAXCODE, STATUS, CREATE_DATE, USER_CREATE)
SELECT ID_SEQ.NEXTVAL,
       REASON_ID,
       TAXCODE,
       1,
       SYSDATE,
       USER_CREATE
FROM TMP_ID_NO_DATA;

COMMIT;

优势： #

• 临时表数据存储在临时表空间，访问速度快
• 一次SELECT插入所有数据，避免多次UNION的开销

方案三：PL/SQL FORALL批量绑定（需要复杂逻辑生成数据） #

如果数据需要通过复杂逻辑生成（比如根据不同规则生成不同TAXCODE），用PL/SQL的FORALL批量插入，减少SQL与PL/SQL的上下文切换：

DECLARE
    TYPE DATA_REC IS RECORD (
        REASON_ID NUMBER(10),
        TAXCODE VARCHAR2(30),
        USER_CREATE VARCHAR2(100)
    );
    TYPE DATA_TAB IS TABLE OF DATA_REC;
    v_data_batch DATA_TAB;
    v_batch_size CONSTANT NUMBER := 10000; -- 每批插入1万条，避免内存溢出
BEGIN
    -- 调整序列缓存
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ID_SEQ CACHE 10000';

    -- 循环生成100批，共100万条
    FOR i IN 1..100 LOOP
        -- 生成当前批次的数据
        SELECT MOD(ROWNUM + (i-1)*v_batch_size, 20)+1, -- 生成1-20的REASON_ID
               'TAX_' || LPAD(ROWNUM + (i-1)*v_batch_size, 9, '0'),
               'abcxyz'
        BULK COLLECT INTO v_data_batch
        FROM DUAL
        CONNECT BY ROWNUM <= v_batch_size;

        -- 批量插入
        FORALL idx IN 1..v_data_batch.COUNT
            INSERT INTO ID_NO_APPROVAL_TEST(ID, REASON_ID, TAXCODE, STATUS, CREATE_DATE, USER_CREATE)
            VALUES(ID_SEQ.NEXTVAL, v_data_batch(idx).REASON_ID, v_data_batch(idx).TAXCODE, 1, SYSDATE, v_data_batch(idx).USER_CREATE);

        COMMIT; -- 每批提交，避免事务过大导致回滚段溢出
    END LOOP;
END;
/

关键优化点： #

• BULK COLLECT一次性把数据加载到PL/SQL集合
• FORALL批量执行INSERT，比单条循环插入快10倍以上

通用优化建议 #

1. 禁用不必要的约束和触发器：插入前可以禁用表上的非主键约束（比如外键、唯一约束）和触发器，插入完成后再启用，避免每条插入都触发额外校验或逻辑
2. 确保表空间充足：直接路径插入需要足够的表空间，提前扩容避免插入中断
3. 避免使用UNION：如果一定要用多SELECT拼接，用UNION ALL代替UNION（不需要去重的话），因为UNION会做排序去重，开销极大

内容的提问来源于stack exchange，提问作者Hieu Pham JR