Oracle 批量投入数据方法总结

零. 待投入数据的表结构

create table DB_USER."PERSON_TABLE" (
  ID NUMBER not null
  , NAME VARCHAR2(50)
  , AGE NUMBER
  , EMAIL VARCHAR2(100)
  , CREATED_DATE DATE
)

一. 😪INSERT INTO ... SELECT投入数据😪

🤔 INSERT INTO ... SELECT的这种方式相当于把数据加载到内存中之后再插入数据库，只适合投入小规模的数据。

1.1 普通的方式投入数据

⏹当数据量不是很多的时候，可以使用这种方式

• 先从DUAL虚拟表中检索后造出指定条数的数据后，再插入到指定的表中。
• 除了主键之类的关键字段之外，其余字段写固定值即可。

INSERT INTO PERSON_TABLE
	SELECT
		-- 因为该字段为字符串形式,所以使用TO_CHAR转换
		-- TO_CHAR(100000000 + LEVEL) || 'TEST_ID' AS id,
		LEVEL AS id,
		'Name_' || ROWNUM AS name,
        TRUNC(DBMS_RANDOM.VALUE(18, 60)) AS age,
        'user' || ROWNUM || '@example.com' AS email,
        SYSDATE - DBMS_RANDOM.VALUE(0, 365) AS created_date
	FROM
		DUAL 
	CONNECT BY LEVEL <= 1000000;

1.2 并行插入（Parallel Insert）投入数据

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(PERSON_TABLE, 4) */ INTO PERSON_TABLE
SELECT LEVEL AS id,
       'Name_' || ROWNUM AS name,
       TRUNC(DBMS_RANDOM.VALUE(18, 60)) AS age,
       'user' || ROWNUM || '@example.com' AS email,
       SYSDATE - DBMS_RANDOM.VALUE(0, 365) AS created_date
FROM DUAL
CONNECT BY LEVEL <= 1000000;

二. 😓PL/SQL 循环投入数据😓

2.1 脚本介绍

• 灵活，支持动态生成数据，适合中小数据量。
• 数据量大时性能较差，容易导致上下文切换开销。

BEGIN
    FOR i IN 1..5000000 LOOP
        INSERT INTO PERSON_TABLE (id, name, age, email, created_date)
        VALUES (
            i,
            'Name_' || i,
            -- 随机年龄
            TRUNC(DBMS_RANDOM.VALUE(18, 60)),
            'user' || i || '@example.com',
            -- 随机日期
            SYSDATE - DBMS_RANDOM.VALUE(0, 365)
        );

        -- 每 100000 条提交一次
        IF MOD(i, 100000) = 0 THEN
            COMMIT;
        END IF;
        
    END LOOP;
    
    COMMIT;
END;
/

2.2 效果

⏹投入500万条数据，耗时5分钟。

三. 💪PL/SQL FORALL 批量操作💪

3.1 脚本介绍

⏹批量插入（FORALL）可以提高效率，减少上下文切换，性能比普通的循环插入要好。

• TYPE person_array IS TABLE OF PERSON_TABLE%ROWTYPE;
  • 定义了一个名称为person_array的集合类型，其中集合中的每个元素的结构与表PERSON_TABLE的一行一致(使用%ROWTYPE)来继承表结构。
• v_data person_array := person_array();
  • 声明了一个变量 v_data，类型为 person_array的集合类型，并将其初始化为空集合。
• v_data.EXTEND;
  • 扩展集合的大小，每次循环新增一个空元素。
• v_data.DELETE;
  • 数据插入完成之后，就清空当前集合，为下一批数据腾出空间。

DECLARE
    TYPE person_array IS TABLE OF PERSON_TABLE%ROWTYPE;
    v_data person_array := person_array();
BEGIN
    FOR i IN 1..5000000 LOOP
        v_data.EXTEND;
        v_data(v_data.COUNT).id := i;
        v_data(v_data.COUNT).name := 'Name_' || i;
        v_data(v_data.COUNT).age := TRUNC(DBMS_RANDOM.VALUE(18, 60));
        v_data(v_data.COUNT).email := 'user' || i || '@example.com';
        v_data(v_data.COUNT).created_date := SYSDATE - DBMS_RANDOM.VALUE(0, 365);

        -- 每 10万 条批量插入一次
        IF MOD(i, 100000) = 0 THEN
            FORALL j IN 1..v_data.COUNT
                INSERT INTO PERSON_TABLE VALUES v_data(j);
            COMMIT;
            -- 数据插入完成之后，就清空当前集合
            v_data.DELETE; 
        END IF;
    END LOOP;

    -- 插入剩余数据
    FORALL j IN 1..v_data.COUNT
        INSERT INTO PERSON_TABLE VALUES v_data(j);
    COMMIT;
END;
/

3.2 效果

⏹投入500万条数据，耗时1分钟18秒。

四. 💪SQL*Loader 工具加载外部文件💪

4.1 脚本介绍

⏹创建生成csv文件的Powershell脚本

⇓⇓⇓详情请参考下面这篇博客⇓⇓
Powershell 生成批量投入数据用的csv文件

⏹创建控制文件control_file.ctl

LOAD DATA
INFILE 'person_data.csv'
INTO TABLE PERSON_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id, name, age, email, created_date "TO_DATE(:created_date, 'YYYY/MM/DD HH24:MI:SS')")

⏹使用 SQL*Loader 执行加载

• 性能极高，适合大规模数据插入。
• 支持多线程和并行加载。

sqlldr db_user/oracle@SERVICE_XEPDB1_CLIENT control=control_file.ctl direct=true

4.2 效果

⏹投入500万条数据，耗时居然不到10秒！💪💪💪