【Oracle专栏】大批量插入数据 BULK COLLECT

 Oracle相关文档，希望互相学习，共同进步

风123456789～-CSDN博客

1.背景

        最近发现从一个表插入另一个表，表行数少的 很快就可以了，但是上十几万、百万的数据，插入会很卡顿，甚至几个小时都无法完成。

    原因：insert插入时没有做批量提交，另外可能导致undo表空间撑爆。

        本文进行优化说明，实现百万、千万级数据的批量提交优化。

2. 实验

2.1 准备表 及数据

待插入的表：SUN_PUBLIC_DATA_TEST

来源表：SUN_PUBLIC_DATA（有百万及大量数据的表）

              表有3400多万数据，直接插入慢的不行。

2.2 将表分区：enum_name, data_issue

分区表语句格式：

create table OPEN_SUN_PUBLIC_DATA
(
  kid         VARCHAR2(32) not null,
  info_id     VARCHAR2(32) not null,
  key         VARCHAR2(255),
  data_issue  VARCHAR2(255),
  data_type   VARCHAR2(255),
  snapshot    CLOB,
  hash_value  VARCHAR2(32),
  date_type   VARCHAR2(255),
  bank_code   VARCHAR2(255) not null,
  create_by   VARCHAR2(32),
  create_time DATE,
  update_by   VARCHAR2(32),
  update_time DATE,
  deleted     VARCHAR2(1),
  line_num    NUMBER,
  enum_name   VARCHAR2(255)
)
PARTITION BY LIST (enum_name, data_issue) (
PARTITION P_ASSETS_FIX_ASSET_202501 VALUES ('ASSETS_FIX_ASSET','2025-01'),
PARTITION P_ASSETS_INTANGIBLE_202501 VALUES ('ASSETS_INTANGIBLE','2025-01'),
PARTITION P_ASSETS_INVENTORY_202501 VALUES ('ASSETS_INVENTORY','2025-01'),
PARTITION P_ASSETS_INVEST_202501 VALUES ('ASSETS_INVEST','2025-01'),
...
PARTITION P_rest VALUES (default)
)
;
create index IDX_SUN_PUBLIC_DATA_INFO_ID on OPEN_SUN_PUBLIC_DATA (INFO_ID);
create index IDX_SUN_PUBLIC_DATA_BANK_CODE on OPEN_SUN_PUBLIC_DATA (BANK_CODE);

alter table OPEN_SUN_PUBLIC_DATA
  add constraint PK_OPEN_SUN_PUBLIC_DATA primary key (KID);

2.3 单独处理某个分区的数据 

只插入某个分区的： 

TRUNCATE TABLE SUN_PUBLIC_DATA_TEST;

INSERT /*+parallel(32)*/INTO SUN_PUBLIC_DATA_TEST
select /*+parallel(32)*/* from SUN_PUBLIC_DATA t 
WHERE T.ENUM_NAME='ASSETS_FIX_ASSET'  AND T.DATA_ISSUE='2025-03';
COMMIT;

select COUNT(*) from SUN_PUBLIC_DATA_TEST t WHERE T.DATA_ISSUE='2025-03';

执行结果：   49万数据，用24s

2.4 批量提交方式的原理

       采用批量提交方式，oracle 的 BULK COLLECT是一个强大的功能，它允许在PL/SQL中批量获取查询结果，而不是逐行处理。这种方式减少了PL/SQL和SQL引擎之间的上下文交换，从而降低了检索数据的开销，可以显著提高处理大量数据时的效率。

       使用BULK COLLECT一次即可提取所有行并绑定到记录