Oracle高效插入100万条逐日变化时间戳记录的方法求助

你当前用单条PL/SQL循环插入的方式，每次都会触发SQL解析和PL/SQL与SQL引擎的上下文切换，100万次循环的开销极大，所以耗时久。以下几种方法能大幅提升插入速度，适合你做分区表和非分区表的性能对比测试：

方法一：FORALL批量插入（PL/SQL优化版） #

通过集合批量存储数据，再一次性提交插入，减少上下文切换和SQL解析次数：

DECLARE
  TYPE t_id_tab IS TABLE OF NUMBER(10) INDEX BY PLS_INTEGER;
  TYPE t_date_tab IS TABLE OF TIMESTAMP WITH TIME ZONE INDEX BY PLS_INTEGER;
  v_ids t_id_tab;
  v_dates t_date_tab;
  v_start_date TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ('2023-05-10 16:32:33.353820500 EUROPE/BERLIN', 'YYYY-MM-DD HH24:MI:SS.FF TZR');
BEGIN
  -- 批量生成数据到集合
  FOR v_LoopCounter IN 1..1000000 LOOP
    v_ids(v_LoopCounter) := v_LoopCounter;
    v_dates(v_LoopCounter) := v_start_date + NUMTODSINTERVAL(v_LoopCounter, 'DAY');
  END LOOP;
  
  -- 批量插入
  FORALL i IN 1..v_ids.COUNT
    INSERT INTO "PAYMENT" (ID, DATETIME) VALUES (v_ids(i), v_dates(i));
  
  COMMIT;
END;
/

如果担心内存占用过高，还可以拆分成多批次（比如每1万条一批）循环插入，避免溢出。

方法二：纯SQL分层查询插入（最快方案） #

利用Oracle的CONNECT BY语法直接生成100万条记录，完全跳过PL/SQL循环，效率最高：

INSERT INTO "PAYMENT" (ID, DATETIME)
SELECT 
  LEVEL AS ID,
  TO_TIMESTAMP_TZ('2023-05-10 16:32:33.353820500 EUROPE/BERLIN', 'YYYY-MM-DD HH24:MI:SS.FF TZR') + NUMTODSINTERVAL(LEVEL, 'DAY') AS DATETIME
FROM DUAL
CONNECT BY LEVEL <= 1000000;

COMMIT;

这种纯SQL操作会触发Oracle最优执行计划，通常几十秒就能完成100万条数据插入。

额外优化建议 #

• 临时关闭表上的触发器、外键约束、唯一约束（测试完成后记得恢复）：这些对象会在插入时触发额外校验或操作，拖慢速度

  -- 关闭触发器
  ALTER TABLE "PAYMENT" DISABLE ALL TRIGGERS;
  -- 关闭指定外键约束（示例）
  ALTER TABLE "PAYMENT" DISABLE CONSTRAINT FK_PAYMENT_XXX;
  
  -- 插入完成后恢复
  ALTER TABLE "PAYMENT" ENABLE ALL TRIGGERS;
  ALTER TABLE "PAYMENT" ENABLE CONSTRAINT FK_PAYMENT_XXX;
  

• 如果是分区表，确保你的DATETIME按天递增的规则和分区键（比如按天分区）匹配，避免跨分区的额外开销。

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