Python+Oracle批量插入优化：450万条数据提速方案问询

核心问题分析 #

你的方案耗时17小时的根本原因是逐条循环执行查询和插入，每次循环都会产生网络往返、SQL解析的额外开销，450万次累加后成本极高。最优思路是把Python端的循环逻辑转移到数据库端，利用Oracle的集合操作能力一次性完成计算和插入，或采用批量操作大幅减少交互次数。

方案一：单条SQL完成全量计算+插入（最快） #

直接在数据库端完成所有逻辑，无需Python循环，这是效率最高的方式：

INSERT INTO table3 (val1, cost, record_number)
SELECT 
    -- 若val1是固定值，直接替换为实际值；若需动态传入，可使用绑定变量
    '固定val1值',
    NVL(
        NVL((SELECT SUM(NVL(cost, 0)) FROM table1 t1 WHERE t1.record_number = d.record_number), 0) +
        NVL((SELECT SUM(NVL(cost, 0)) FROM table2 t2 WHERE t2.record_number = d.record_number), 0),
        0
    ) AS total_cost,
    d.record_number
FROM descriptions d;

关键优化点：

1. 消除Python与数据库的450万次网络交互，所有计算在数据库内部完成，速度提升几个数量级。
2. 若table1和table2的record_number字段无索引，必须先创建索引，否则SUM查询会全表扫描，严重拖慢效率：

   CREATE INDEX idx_table1_record ON table1(record_number);
   CREATE INDEX idx_table2_record ON table2(record_number);
   

3. 可选使用APPEND提示加速插入（适合无事务要求的场景，减少日志写入）：

   INSERT /*+ APPEND */ INTO table3 (val1, cost, record_number)
   -- 后续SELECT逻辑同上
   

方案二：Python端批量操作（若必须保留Python处理逻辑） #

如果业务上需要在Python端做额外处理，可通过以下方式大幅优化：

1. 批量查询+批量计算

• 不要一次性fetchall()，而是分批获取数据（比如每次取10000条），避免内存溢出：

  cursor.arraysize = 10000  # 设置批量获取的大小
  while True:
      records = cursor.fetchmany()
      if not records:
          break
      # 批量处理当前批次的record_number
  

• 用IN子句批量查询table1和table2的cost，代替逐条查询：

  # 提取当前批次的record_number列表
  record_numbers = [r[0] for r in records]
  # 批量查询table1的sum cost
  query_t1 = """
      SELECT record_number, SUM(NVL(cost, 0)) AS total_cost
      FROM table1
      WHERE record_number IN ({})
      GROUP BY record_number
  """.format(','.join([':%d' % i for i in range(len(record_numbers))]))
  cursor.execute(query_t1, record_numbers)
  t1_costs = {row[0]: row[1] for row in cursor.fetchall()}
  
  # 同理批量查询table2的sum cost并整理为字典
  # 合并计算每个record_number的总cost
  

2. 批量插入

使用executemany()批量插入，减少提交次数：

# 关闭自动提交，手动批量提交
connection.autocommit = False

# 准备批量插入的数据列表
batch_data = []
for record_number in record_numbers:
    total_cost = t1_costs.get(record_number, 0) + t2_costs.get(record_number, 0)
    batch_data.append(('val1值', total_cost, record_number))

# 批量插入
insert_query = "INSERT INTO table3 VALUES (:1, :2, :3)"
cursor.executemany(insert_query, batch_data)
connection.commit()  # 每批次提交一次

3. 额外优化项

• 设置cursor.arraysize和cursor.prefetchrows为较大值（如10000），提升数据传输效率。
• 插入前临时禁用table3的索引和约束，插入完成后重新启用：

  ALTER INDEX idx_table3_record_number DISABLE;
  -- 插入完成后
  ALTER INDEX idx_table3_record_number REBUILD;
  

效果对比 #

• 方案一（单SQL）：450万条数据通常可在5-30分钟内完成（取决于数据库硬件和索引情况）。
• 方案二（Python批量）：可将耗时压缩到1-2小时内，远优于原17小时的效率。

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