C#+“外部表格”實現Oracle資料快速插入

 

    Oracle是大型資料庫，可以用於儲存海量資料。對於資料的來源，也有多種途徑，其中有一部分是隨著業務的發展不斷添加進來的，也有在業務系統初始化的時候，大量匯入進來的。對於不斷添加這個過程，不在此進行描述，只對大量匯入做一個簡單的說明。

    以下涉及到的開發環境為：VS2008 + Oracle9i

    對於大量匯入有多種方式，各種方式的操作方式及效率也各不相同，下面我們來做一個簡單的測試。

    一．準備工作

    1.先要在Oracle中建一個測試表供插入使用，可以建三個欄位，SQL語句請參考：

    create table TEST
    (
    ID VARCHAR2(100),
    NAME VARCHAR2(100),
    DOB DATE
    )

    在上例中，我特意做了一個日期型的欄位，因為日期型的欄位涉及到一個格式問題，比較複雜，所以特意在此說明。

    2.準備批量資料

   上面已經建好了測試表，下面就要準備一些測試資料準備插入之用，檔案的格式如下：

    1~name:1~2009-04-10 10:00:00

    2~name:3~2009-04-10 10:00:00

    3~name:3~2009-04-10 10:00:00

    以上資料全部為測試資料，沒有任何實際含義，並且每個欄位之間用~來分隔。之所有沒有用傳統的逗號作分隔字元，是考慮字串中可能會出現這個逗號，以免引起混淆。

    第一次我們先準備50萬條記錄作測試，以免壓力太大系統不能承受，因為我的測試機是一個很老的筆記本，效能非常差勁。

    二．插入方式對比

    上面準備了測試資料，下面就要來把這些資料插入到第一步建的測試表中，對於如何插入，實在是有太多的方式了，我只挑選兩種比較極端的情況來做個比較

    1.使用外部程式來處理插入（C#）

    這是一種傳統的做法，使用ODBC/OleDB等方式與資料庫連接，並使用標準的insert進行插入操作。為了實現這種方式，需要把文字檔每一行讀出來，把各個欄位拆解開，再拼接成SQL語句，從而實現資料的插入，簡單的程式片斷如下：

DataAccessor data = new DataAccessor();

string sql = "truncate table test";

data.ExecuteNonQuery(sql);

System.Diagnostics.Debug.WriteLine(System.DateTime.Now.ToString());

System.IO.StreamReader reader = new System.IO.StreamReader("c:\\temp\\data.txt");

string line = "";

while (line != null)

{

line = reader.ReadLine();

if (line != null)

{

string[] lines = line.Split('~');

sql = "insert into test (id,name,dob) values(" + lines[0]

+ ",'" + lines[1] + "',to_date('" + lines[2]

+ "','yyyy-mm-dd hh24:mi:ss'))";

data.ExecuteNonQuery(sql);

if ((int.Parse(lines[0]) % 10000) == 0)

{

System.Diagnostics.Debug.WriteLine(lines[0]);

}

}

}

System.Diagnostics.Debug.WriteLine(System.DateTime.Now.ToString());

    從上述代碼，可以很清楚的看出讀檔案及拆解插入的過程，不再過多的說明。

    這種方式的特點是插入的中間過程可以控制，可以加上人機互動，可以知道插入的狀態，並且可以隨時停止插入的過程，但是缺點是速度比較慢。

    2.使用外部表格的方式來插入

    所謂外部表格，是用於區分Oracle的普通表的一種格式。普通的表都是建立在資料庫的內部，資料存放區也是在Oracle的自身資料檔案中，而外部表格，則是類似一個指標，直接指向外部物理檔案，比如上面測試用的data.txt，可以直接映射成一個外部表格。

    使用外部表格的方式我感覺在原理上與第一種方式沒有太大的本質區別，只是所有這些中間處理的過程全部由Oracle自己來完成，它很清楚怎麼做效能會比較好，所以這種方式是不錯的選擇。

    外部一般檔案本身只是儲存資料，並不能對欄位等資訊進行自描述，所以還需要在引用的時候，強行指定文字檔的格式，這樣就能“自圓其說”了。

    在建立外部表格之前，還要先聲明一點：Oracle是一個獨立的資料庫系統，它的所有操作全是在它自己的進程中完成，因此如果需要引用外部作業系統的檔案，必須通知它，再加上一些許可權上的考慮，還需要做一些特別的配置才可以實現上述的功能，主要的動作包括以下幾步：

    I． 增加Oracle對檔案指定目錄的許可權

    Oracle資料庫能訪問哪些作業系統的目錄，必須提前指定好，否則是沒有許可權的，這個指定需要修改Oracle的一個初始參數，比如我把一般檔案放在了c:\temp的目錄下，就要這樣修改：

    alter system set utl_file_dir='c:\temp' scope=spfile;

    這裡有一點需要記住，修改這個參數後，資料庫必須重啟才會生效。

    II． 建立一個內部目錄

    重起資料庫後，就可以在Oracle內建一個目錄的引用，這個引用將直接指向外部的目錄，如：

   create directory temp as ‘c:\temp’;

    為什麼要這樣做呢，其實也就是封裝一下，因為在程式中不能直接引用作業系統的路徑名，這樣封裝一下後，直接引用temp就可以了。

    經過以上兩步的準備工作，正式的建表工作就要開始嘍！看看下面的SQL，是不是有點眼暈：

create table zr_user_temp_ext(

USER_ID VARCHAR2(20) ,

USER_ALIAS VARCHAR2(20),

QQ date)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY temp

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY '~'

MISSING FIELD VALUES ARE NULL

(user_id,user_alias,

qq date "YYYY-MM-DD HH24:MI:SS"

)

)

LOCATION('data.txt')

)

下面我來對上面的SQL中的幾個主要部分做個說明：

Ø Create table：

這部分代碼與標準的表一樣，並且在裡面指定欄位名等內容，沒有特別的地方

Ø ORGANIZATION EXTERNAL

這個子句就表明現在聲明的是一個外部表格而不是一張普通的表噢

Ø DEFAULT DIRECTORY temp

這個子句指定外部表格的檔案在哪個目錄中取得

Ø RECORDS DELIMITED BY NEWLINE

這個子句說明文字檔中的每一行就是一個記錄。但是當資料庫伺服器的作業系統不同的時候，這個文字檔的分行符號也需要特別注意一下，因為在NT系統裡，換行採用\n\r雙位元組來表示，而在UNIX/LINUX系統下，換行只用一個位元組來表示，所以如果是從NT系統產生的檔案，傳到LINUX進行處理的時候，有可能就會出問題。

Ø FIELDS TERMINATED BY '~'

這個子句用於表示各個欄位間用什麼來分隔，根據上面檔案的格式，可以看出這個子句的含義。

Ø MISSING FIELD VALUES ARE NULL

這個子句說明如果一個記錄中某個欄位的值沒有，則按“空”來處理

qq date "YYYY-MM-DD HH24:MI:SS"

這個子句也比較有用，它用於指定日期型欄位的格式碼，這個格式碼將直接與檔案中的格式相對應，這樣才能實現資料的正確讀取和匯入。

Ø LOCATION('data.txt')

這個子句用於指明外部檔案的檔案名稱，與目錄名拼接在一起，就可以在作業系統中對其進行精確的定位了。

此外，還有很多的參數，我這裡都沒有寫，全部採用了預設值，我也沒有太關心過其它參數，能用就行了，呵。

還需要注意一點，這個SQL只檢查語法錯誤，而對於物理檔案是否存在，它並不做任何檢查，因為此需要大家自己把握好這一點。

好了，到此檔案，我們偉大的外部表格已經建立完成了，來試一下吧：

select * from test_ext;

如果不出意外，您會看到，一般檔案已經用表的形式展現在您的面前了，哈，真是很開心吧。但是到目前為止，雖然我們能以表的形式來展現資料，但是資料實際上還是儲存於外部的，還需要把它實際的匯入進來才可以。這個匯入就更簡單了，比如：

insert into test select * from test_ext；

就這麼簡單，外部表格在使用起來和內部表沒什麼區別。

當然還可以再加上hint功能，讓這個插入更加快速。

    三．選擇適合自己的方式

    上面只介紹了兩種方式，除此之外，還可以用sql loader等其它方式，也可以在預存程序中對檔案進行拆解插入，這兩種方式我都實驗過了，與外部表格的效能類似，但是使用更加麻煩，也不便於程式調用，所以我推薦外部表格的方式。

    在資料量較小的時候，比如100條記錄，幾種方式真的沒有太大區別，1秒和0.01秒對於客戶來說，沒有什麼實質的差異，但是如果是50萬或更多的記錄數，就要考慮這個問題了。下面是我的幾個測試資料可以供大家參考：

插入方式	50萬條	500萬條
C#	17分鐘	未測試
外部表格	4.8秒	48秒

    如果您關心效能，從上表可以很明顯找到適合您的匯入方案了。

    四．結論

    雖然外部表格的方式效率非常高，但是操作複雜也是它的一個弊端，它會給您的應用程式帶來很多不必要的麻煩，而且如果應用程式與資料庫伺服器不在一起，甚至作業系統都不一樣，還要增加上傳檔案的操作，幾個步驟之間的協調關係也需要做很多的考慮。

    總之，上述方式是一個不錯的方式，僅供大家選擇。