Oracle 批量投入数据方法总结
目录
- 零. 待投入数据的表结构
- 一. `INSERT INTO ... SELECT`投入数据
- 1.1 普通的方式投入数据
- 1.2 并行插入（Parallel Insert）投入数据
- 二. PL/SQL 循环投入数据
- 2.1 脚本介绍
- 2.2 效果
- 三. 💪PL/SQL FORALL 批量操作💪
- 3.1 脚本介绍
- 3.2 效果
- 四. 💪SQL*Loader 工具加载外部文件💪
- 4.2 效果
零. 待投入数据的表结构
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 脚本介绍
- 这种方式可以
减少上下文切换，性能比普通的循环插入要好。
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文件。
# 文件名称
$file_name = 'person_data.csv'
# 路径
$outputFile = "$Home\Desktop\$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
}
<#
将生成的内容写入文件
-NoNewline 的作用是为了防止csv文件的最后一行被追加空行
#>
$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
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秒！💪💪💪
