C#+“外部表”实现Oracle数据快速插入

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秒

    如果您关心性能，从上表可以很明显找到适合您的导入方案了。

    四．结论

    虽然外部表的方式效率非常高，但是操作复杂也是它的一个弊端，它会给您的应用程序带来很多不必要的麻烦，而且如果应用程序与数据库服务器不在一起，甚至操作系统都不一样，还要增加上传文件的操作，几个步骤之间的协调关系也需���做很多的考虑。

    总之，上述方式是一个不错的方式，仅供大家选择。

天道酬勤不酬怨
李鸣(aicken)原创 转载注明