Oracle批量投入数据方法总结

零. 待投入数据的表结构

1
2
3
4
5
6
7
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虚拟表中检索后造出指定条数的数据后,再插入到指定的表中。
  • 除了主键之类的关键字段之外,其余字段写固定值即可。
1
2
3
4
5
6
7
8
9
10
11
12
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

1.2 并行插入(Parallel Insert)投入数据

1
ALTER SESSION ENABLE PARALLEL DML;
1
2
3
4
5
6
7
8
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

二. PL/SQL 循环投入数据

2.1 脚本介绍

  • 灵活,支持动态生成数据,适合中小数据量
  • 数据量大时性能较差,容易导致上下文切换开销
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 脚本介绍

  • 这种方式可以减少上下文切换,性能比普通的循环插入要好。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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);
 
        -- 每 100000 条批量插入一次
        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 工具加载外部文件

写一个PowerShell脚本,根据数据库的表结构来生成csv文件

  • 该脚本执行后,会在桌面上生成一个csv文件。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
# 文件名称
$file_name = 'person_data.csv'
# 路径
$outputFile = "$HomeDesktop$file_name"
# csv 文件的总行数
$rows = 5000000
# 并行线程数
$threadCount = 4
# 每个线程生成的记录数量
$chunkSize = [math]::Ceiling($rows / $threadCount)
 
# 判断文件是否存在,存在的话就删除
if (Test-Path -Path $outputFile) {
    Remove-Item -Path $outputFile -Force
}
 
# 写入 CSV 表头
# "`"ID`",`"NAME`",`"AGE`",`"EMAIL`",`"CREATED_DATE`"" | Out-File -FilePath $outputFile -Encoding UTF8 -Append
 
# 定义脚本块
$scriptblock = {
     
    param($startRow, $endRow, $tempFile)
 
    # 在后台作业中定义 Generate-Chunk 函数
    function Generate-Chunk {
         
        param (
            [int]$startRow,
            [int]$endRow,
            [string]$filePath
        )
 
        $random = [System.Random]::new()
        $currentDate = Get-Date
        $sb = [System.Text.StringBuilder]::new()
 
        # 循环生成csv数据
        for ($i = $startRow; $i -le $endRow; $i++) {
             
            # =========================对应数据库的各字段值=========================
            $id = $i
            $name = "Name_$i"
            $age = $random.Next(18, 60)
            $email = "user$i@example.com"
            $createdDate = $currentDate.AddDays(- $random.Next(0, 365)).ToString("yyyy/MM/dd HH:mm:ss")
            # =========================对应数据库的各字段值=========================
             
            # =========================一行csv=========================
            $line = "`"$id`",`"$name`",`"$age`",`"$email`",`"$createdDate`""
            # =========================一行csv=========================
 
            $sb.AppendLine($line) | Out-Null
        }
 
         
        $sb.ToString() | Out-File -FilePath $filePath -Encoding UTF8 -Append -NoNewline
    }
 
    # 调用 Generate-Chunk 函数,多线程生成临时csv文件
    Generate-Chunk -startRow $startRow -endRow $endRow -filePath $tempFile
}
 
# CSV文件合成
function Merge-CSV {
         
    param (
        [string]$outputFile,
        [bool]$IsReadAllDataToMemory
    )
     
    # 获取所有分段文件,按名称排序
    $partFiles = Get-ChildItem -Path "$outputFile.*.part" | Sort-Object Name
     
    if ($IsReadAllDataToMemory) {
         
        # 将所有内容加载到内存中,然后一次性写入
        $partFiles | ForEach-Object { Get-Content $_.FullName } | Out-File -FilePath $outputFile -Encoding UTF8 -Force
        # 删除所有分段文件
        $partFiles | ForEach-Object { Remove-Item $_.FullName }
         
        return;
    }
     
    $partFiles | ForEach-Object {
        Get-Content -Path $_.FullName | Out-File -FilePath $outputFile -Encoding UTF8 -Append
        Remove-Item -Path $_.FullName
    }
}
     
try {
    # 定义job数组
    $jobs = @()
     
    # 组装job
    1..$threadCount | ForEach-Object {
         
        $startRow = ($_ - 1) * $chunkSize + 1
        $endRow = [math]::Min($_ * $chunkSize, $rows)
         
        # 临时csv文件
        $tempFile = "$outputFile.$_.part"
 
        $jobs += Start-Job -ScriptBlock $scriptblock -ArgumentList $startRow, $endRow, $tempFile
    }
     
    # 统计生成csv文件所消耗的时间
    $exec_time = Measure-Command {
 
        Write-Host "临时csv文件开始生成..."
 
        # 执行job,等待并收集所有执行结果
        $jobs | ForEach-Object { Wait-Job -Job $_; Receive-Job -Job $_; Remove-Job -Job $_ }
 
        # 合并所有并发生成的csv临时文件,组装成最终的总csv文件
        Write-Host "临时csv文件生成完毕,开启合并..."
        Merge-CSV -outputFile $outputFile -IsReadAllDataToMemory $False
    }
 
    Write-Host "csv文件生成完毕,共消耗$($exec_time.TotalSeconds)秒: $outputFile" -ForegroundColor Red
     
} catch {
 
    # 当异常发生时,清空桌面上的临时csv文件
    if (Test-Path -Path "$outputFile.*.part") {
        Remove-Item -Path "$outputFile.*.part" -Force
    }
 
    Write-Host "脚本运行时发生异常: $_" -ForegroundColor Red
    Write-Host "详细信息: $($_.Exception.Message)" -ForegroundColor Yellow
    Write-Host "堆栈跟踪: $($_.Exception.StackTrace)" -ForegroundColor Gray
}
 
Read-Host "按 Enter 键退出..."

创建控制文件control_file.ctl

1
2
3
4
5
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 执行加载

  • 性能极高,适合大规模数据插入。
  • 支持多线程和并行加载。
1
sqlldr db_user/oracle@SERVICE_XEPDB1_CLIENT control=control_file.ctl direct=true

4.1 效果

投入500万条数据,耗时居然不到10秒!

到此这篇关于Oracle批量投入数据方法总结的文章就介绍到这了,更多相关Oracle投入数据内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部!

本文收集自网络,不代表IT俱乐部立场,转载请注明出处。https://www.2it.club/database/oracle/15560.html
上一篇
下一篇
联系我们

联系我们

在线咨询: QQ交谈

邮箱: 1120393934@qq.com

工作时间:周一至周五,9:00-17:30,节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部