零. 待投入数据的表结构
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俱乐部!