使用 Oracle SQL Loader 快速載入資料進 DB

一般寫程式遇到需要新增資料到 DB 中，大部分的寫法都是直接 Insert Into [TableName] ...

以 .Net 來說大概就是使用 ADO .Net 來處理，如


string sql="";
string connectionString =
"Data Source=[DB];" +
"User ID=[ACT];" +
"Password=[PWD];";
// Create Connection
OracleConnection dbcon = null;
dbcon = new OracleConnection (connectionString);
dbcon.Open();

// Generate Command Text
OracleCommand dbcmd = dbcon.CreateCommand();
......
// [先 Select 資料再跑迴圈產生 Insert Statement]
for (...)
{
......
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();
}

// Relese Resource
dbcmd.Dispose ();
dbcmd = null;
dbcon.Close ();
dbcon = null;


然後視情況看是一筆一筆 Commit 還是弄成一個 Transation ，全部完成之後再 Commit 。

但是當資料量大到一定程度的時候 ( 例如批次作業將大量Log資料新增進DB ) 就不一定可行。


另外一種方式就是把全部的 Insert Statement 一次產生出來放到 Text File，


...以上省略...

// 原本 For 迴圈變成僅產生 Insert Statement
StringBuilder mTextContent = new StringBuilder();
for (...)
{
......
mTextContent.Append(sql);
}

// 全部產生完後，放入 Text File 內
using (System.IO.StreamWriter sw = new System.IO.StreamWriter("[FileName]", false))
{
sw.Write(mAllContent);
sw.Close();
}

...以下省略...


然後從 Console 程式裡面多開一個 Process 呼叫 命令提示字元(Command Line) 來執行 SqlPlus 處理所產生的 Text File。


...
System.Diagnostics.ProcessStartInfo PSI = new System.Diagnostics.ProcessStartInfo("cmd.exe");
PSI.RedirectStandardInput = true;
PSI.RedirectStandardOutput = false;
PSI.RedirectStandardError = true;
PSI.UseShellExecute = false;
PSI.WorkingDirectory = [Path];
System.Diagnostics.Process p = System.Diagnostics.Process.Start(PSI);
p.Start();
// ConnectionString 來自 Web.Config 的連線字串 (格式調整成 ACT/PWD@DB_TNS 而已)
p.StandardInput.WriteLine("sqlplus " + ConnectionString + " @\"[FileName\" ");
p.StandardInput.WriteLine("exit");
p.WaitForExit();
p.Close();
p.Dispose();
...


一般只要資料量沒有太大，這個方式就可以處理了。

但是還有一個更快的方法，就是透過 SQL Loader (MS Server好像是 Data Transformation Services;DTS )

SQL Loader 是 Oracle 的資料匯入工具，通常用來將大量的資料新增進 Oracle 資料庫中。

基本上可以分成三個部份：

• Control file (控制檔，定義匯入Table、欄位對應與分隔符號...等)


• Data file (資料檔，格式須參照控制檔定義)


• Sqlldr (執行檔，可搭配一些參數彈性使用)

◎ Control File

大致內容如下：

load data
infile '[資料檔名]'	尚可指定 discardfile result.dsc badfile result.bad (沒有指定則同資料檔名)
append into table "[TableName]"	預設為 Insert，可改為 Append/Truncate/Replace
fields terminated by X'09' Optionally enclosed by '"'	欄位間用 Tab 作分隔 (X'09')；而字串值用雙引號括起來 (")
(	以下為對應的欄位定義
SID INTEGER EXTERNAL,	數字欄位
SNO FLOAT,	浮點數欄位
SNAME CHAR(4000),	字串欄位
SDATE Date "yyyy/mm/dd hh24:mi:ss",	日期欄位以及格式
EDATE Date "yyyy/mm/dd" NULLIF (EDATE="null")	日期欄位，遇到空值則塞null值
)

◎ Data File

參照 Control File 後，大致內容會如下：(分隔是 Tab)
1 1.1 "名稱1" "2011/03/03 01:20:30" "2011/03/05"
2 2.1 "名稱2" "2011/04/03 12:24:00" "2011/04/05"
3 3.1 "名稱3" "2011/05/03 15:16:17" "2011/05/05"

◎ Sqlldr 的語法

sqlldr userid=[帳號]/[密碼]@資料庫名稱 control=[控制檔名] log=[產生的Log檔名]

可以加上兩個參數

Rows=300 (每 300 個一次載入到資料庫中)

errors=100 (遇到 100 個錯誤就終止執行)

.

Log 內容摘要
(大約19個欄位，88111筆資料，Txt檔案大小21mb，Remote執行-Data&Control File不在DB Server上)

表格 "TEST_LIST":
已順利載入 88111 資料列.
由於資料錯誤, 0 資料列 未被載入
因為所有的 WHEN 子句均不成立, 0 資料列 未被載入
因為所有的欄位均為 NULL, 0 資料列 未被載入


分配給連結陣列的空間: 246294 個位元組 (9 列)
讀取 緩衝區位元組: 1048576

略過的邏輯記錄總數: 0
讀取的邏輯記錄總數: 88111
拒絕的邏輯記錄總數: 0
刪除的邏輯記錄總數: 0

自 星期三 4月 20 22:37:22 2011 開始執行
於 星期三 4月 20 22:41:18 2011 結束執行

目前歷時: 00:03:56.20
CPU 時間為: 00:00:03.75

單純全部載入 88111 筆資料列只要 4 分鐘的時間。

所以搭配程式使用，先按照控制檔定義的格式，輸出要匯入資料文字檔
再調整把 sqlplus 換成 sqlldr 即可

...
p.StandardInput.WriteLine("sqlldr userid=" + ConnectionString + " control=[控制檔] log=[Log檔] rows=300 errors=100");
...