www.9778.com 3

www.9778.com探讨Oracle数据库的数据导入方法

每个数据库管理员都会面临数据导入的问题,这有可能发生在数据库的新老移植过程中,或者是在数据库崩溃后的恢复重建过程中,还有可能是在创建测试数据库的模拟环境过程中,总之作为一名合格的数据库管理员,你应该做好接受各种数据导入请求的技术储备,同时还要尽量满足人本能的对导入速度的苛求。本文仅针对
Oracle
数据库所提供的加速数据导入的各种特性和技术进行探讨,其中的一些方法也可以转化应用于其他数据库。以下七种数据导入方法哪个最适用需要针对具体情况具体分析,我也附带列举了影响导入速度的各种因素供斟酌。为了比较各种数据导入方法的效果,我创建了示例表和数据集,并用各种方法导入示例数据集来计算总体导入时间和导入进程占用
CPU 时间,这里得出的时间仅供参考。需要说明的是,建议你使用 Oracle 9i
企业版数据库,当然你也可以尝试使用 Oracle 7.3
以上的标准版数据库。本文使用的机器配置为:CPU Intel P4,内存
256M,数据库 Oracle 9i 企业版。

示例表结构和数据集

命名空间:Oracle.DataAccess.Client

示例表结构和数据集

为了演示和比较各种数据导入方法,我假定数据导入任务是将外部文件数据导入到
Oracle 数据库的CALLS表中,外部数据文件包含十万条呼叫中心记录,将近 6MB
的文件大小,具体的数据示例如下:

组件:Oracle.DataAccess.dll(2.112.1.0)

为了演示和比较各种数据导入方法,我假定数据导入任务是将外部文件数据导入到
Oracle 数据库的CALLS表中,外部数据文件包含十万条呼叫中心记录,将近 6MB
的文件大小,具体的数据示例如下:

82302284384,2003-04-18:13:18:58,5001,投诉,手机三包维修质量82302284385,2003-04-18:13:18:59,3352,咨询,供水热线的号码82302284386,2003-04-18:13:19:01,3142,建议,增设公交线路

ODP.NET 版本:ODP.NET for .NET Framework 2.0 或 ODP.NET for .NET
Framework 4

82302284384,2003-04-18:13:18:58,5001,投诉,手机三包维修质量82302284385,2003-04-18:13:18:59,3352,咨询,供水热线的号码82302284386,2003-04-18:13:19:01,3142,建议,增设公交线路

接受导入数据的表名是 CALLS,表结构如下:

工具:Microsoft Visual Studio Ultimate 2013 + Oracle SQL Developer
1.5.5 + Oracle Database 11g Enterprise Edition 11.2.0.1.0(32位)
+ TNS for 32-bit Windows 11.2.0.1.0

接受导入数据的表名是 CALLS,表结构如下:

Name Null? Type Comment———— ——— ————-
—————–CALL_ID NOT NULL NUMBER Primary keyCALL_DATE NOT NULL
DATE Non-unique indexEMP_ID NOT NULL NUMBERCALL_TYPE NOT NULL
VARCHAR2(12)DETAILS VARCHAR2(25)

 

Name Null? Type Comment———— ——— ————-
—————–CALL_ID NOT NULL NUMBER Primary keyCALL_DATE NOT NULL
DATE Non-unique indexEMP_ID NOT NULL NUMBERCALL_TYPE NOT NULL
VARCHAR2(12)DETAILS VARCHAR2(25)

逐条数据插入INSERT

方式一:ArrayBind

当插入一条数据时,SQL 语句如下:

INSERT INTO table_name VALUES (:col1, :col2, :col3, :col4, :col5)

  1 public void InsertDataRow(Dictionary<string, object> dataRow)
  2 {
  3     StringBuilder sbCmdText = new StringBuilder();
  4     sbCmdText.AppendFormat("INSERT INTO {0}(", m_TableName);
  5     sbCmdText.Append(string.Join(",", dataRow.Keys.ToArray()));
  6     sbCmdText.Append(") VALUES (");
  7     sbCmdText.Append(":" + string.Join(",:", dataRow.Keys.ToArray()));
  8     sbCmdText.Append(")");
  9 
 10     using (OracleConnection conn = new OracleConnection())
 11     {
 12         using (OracleCommand cmd = conn.CreateCommand())
 13         {
 14             cmd.CommandType = CommandType.Text;
 15             cmd.CommandText = sbCmdText.ToString();
 16             OracleParameter parameter = null;
 17             OracleDbType dbType = OracleDbType.Object;
 18             foreach (string colName in dataRow.Keys)
 19             {
 20                 dbType = GetOracleDbType(dataRow[colName]);
 21                 parameter = new OracleParameter(colName, dbType);
 22                 parameter.Direction = ParameterDirection.Input;
 23                 parameter.OracleDbTypeEx = dbType;
 24                 parameter.Value = dataRow[colName];
 25                 cmd.Parameters.Add(parameter);
 26             }
 27             conn.Open();
 28             int result = cmd.ExecuteNonQuery();
 29         }
 30     }
 31 }

此时,每一个 OracleParameter 的 Value 值都赋予单个字段的
一个具体值,这种也是最为传统的插入数据的方法。

Oracle V6 中 OCI 编程接口加入了数组接口特性。

当采用 ArrayBind 时,OraleParameter 的 Value 值则是赋予单个字段的
一个数组,即多条数据的该字段组合成的一个数组。此时 Oracle 仅需要执行一次
SQL
语句,即可在内存中批量解析并导入数据,减少程序与数据库之间来回的操作,其优点就是数据导入的总体时间明显减少,尤其是进程占用
CPU 的时间。

如果数据源是 DataTable 类型,首先把 DataTable 数据源,转换成
object[][] 类型,然后绑定 OracleParameter 的 Value
值为对应字段的一个 Object[] 数组即可;参考代码如下:

  1 /// <summary>
  2 /// 批量插入大数据量
  3 /// </summary>
  4 /// <param name="columnData">列名-列数据字典</param>
  5 /// <param name="dataCount">数据量</param>
  6 /// <returns>插入数据量</returns>
  7 public int InsertBigData(Dictionary<string, object> columnData, int dataCount)
  8 {
  9     int result = 0;
 10     if (columnData == null || columnData.Count < 1)
 11     {
 12         return result;
 13     }
 14     string[] colHeaders = columnData.Keys.ToArray();
 15     StringBuilder sbCmdText = new StringBuilder();
 16     if (columnData.Count > 0)
 17     {
 18         // 拼接INSERT的SQL语句
 19         sbCmdText.AppendFormat("INSERT INTO {0}(", m_TableName);
 20         sbCmdText.Append(string.Join(",", colHeaders));
 21         sbCmdText.Append(") VALUES (");
 22         sbCmdText.Append(m_ParameterPrefix + string.Join("," + m_ParameterPrefix, colHeaders));
 23         sbCmdText.Append(")");
 24         OracleConnection connection = null;
 25         try
 26         {
 27             connection = new OracleConnection(GetConnectionString());
 28             using (OracleCommand command = connection.CreateCommand())
 29             {
 30                 command.ArrayBindCount = dataCount;
 31                 command.BindByName = true;
 32                 command.CommandType = CommandType.Text;
 33                 command.CommandText = sbCmdText.ToString();
 34                 command.CommandTimeout = 1800;
 35                 OracleParameter parameter;
 36                 OracleDbType dbType = OracleDbType.Object;
 37                 foreach (string colName in colHeaders)
 38                 {
 39                     dbType = GetOracleDbType(columnData[colName]);
 40                     parameter = new OracleParameter(colName, dbType);
 41                     parameter.Direction = ParameterDirection.Input;
 42                     parameter.OracleDbTypeEx = dbType;
 43                     parameter.Value = columnData[colName];
 44                     command.Parameters.Add(parameter);
 45                 }
 46                 connection.Open();
 47                 OracleTransaction trans = connection.BeginTransaction();
 48                 try
 49                 {
 50                     command.Transaction = trans;
 51                     result = command.ExecuteNonQuery();
 52                     trans.Commit();
 53                 }
 54                 catch (Exception ex)
 55                 {
 56                     trans.Rollback();
 57                     throw ex;
 58                 }
 59             }
 60         }
 61         finally
 62         {
 63             if (connection != null)
 64             {
 65                 connection.Close();
 66                 connection.Dispose();
 67             }
 68             GC.Collect();
 69             GC.WaitForFullGCComplete();
 70         }
 71     }
 72     return result;
 73 }

www.9778.com 1www.9778.com 2

  1 /// <summary>
  2 /// 根据数据类型获取OracleDbType
  3 /// </summary>
  4 /// <param name="value">数据</param>
  5 /// <returns>数据的Oracle类型</returns>
  6 private static OracleDbType GetOracleDbType(object value)
  7 {
  8     OracleDbType dataType = OracleDbType.Object;
  9     if (value is string[])
 10     {
 11         dataType = OracleDbType.Varchar2;
 12     }
 13     else if (value is DateTime[])
 14     {
 15         dataType = OracleDbType.TimeStamp;
 16     }
 17     else if (value is int[] || value is short[])
 18     {
 19         dataType = OracleDbType.Int32;
 20     }
 21     else if (value is long[])
 22     {
 23         dataType = OracleDbType.Int64;
 24     }
 25     else if (value is decimal[] || value is double[] || value is float[])
 26     {
 27         dataType = OracleDbType.Decimal;
 28     }
 29     else if (value is Guid[])
 30     {
 31         dataType = OracleDbType.Varchar2;
 32     }
 33     else if (value is bool[] || value is Boolean[])
 34     {
 35         dataType = OracleDbType.Byte;
 36     }
 37     else if (value is byte[])
 38     {
 39         dataType = OracleDbType.Blob;
 40     }
 41     else if (value is char[])
 42     {
 43         dataType = OracleDbType.Char;
 44     }
 45     return dataType;
 46 }

GetOracleDbType

说明:如果采用分次(每次1万数据)执行 InsertBigData
方法,速度反而比一次性执行 InsertBigData 方法慢,详见下面测试结果;

测试结果:

无索引,数据类型:4列NVARCHAR2,2列NUMBER

30+万(7.36M):一次性导入用时 15:623,每次10000导入用时

60+万(14.6M):一次性导入用时 28:207,每次10000导入用时 1:2:300

100+万(24.9M):一次性导入报如下异常

www.9778.com 3

此时实际上从资源监视器上可以得知仍有可用内存,但是仍旧报
OutOfMemoryException,所以猜测应该是一个 bug;

如果每次10000导入用时 2:9:252

如果每次50000导入用时 58:101

附加 InsertBigData 方法使用示例:

www.9778.com 4www.9778.com 5

  1 // 每10000数据导入一次
  2 Dictionary<string, object> columnsData = new Dictionary<string, object>();
  3 int dataCount = m_SourceDataTable.Rows.Count;
  4 int times = dataCount / 10000 + (dataCount % 10000 == 0 ? 0 : 1);
  5 for (int i = 0; i < times; i++)
  6 {
  7     int startIndex = i * 10000;
  8     int endIndex = (i + 1) * 10000;
  9     endIndex = endIndex > dataCount ? dataCount : endIndex;
 10     int currDataCount = endIndex - startIndex;
 11     columnsData.Add("COL1", new string[currDataCount]);
 12     columnsData.Add("COL2", new string[currDataCount]);
 13     columnsData.Add("COL3", new decimal[currDataCount]);
 14     columnsData.Add("COL4", new string[currDataCount]);
 15     columnsData.Add("COL5", new decimal[currDataCount]);
 16     columnsData.Add("COL6", new string[currDataCount]);
 17     for (int rowIndex = startIndex; rowIndex < endIndex; rowIndex++)
 18     {
 19         int dicRowIndex = rowIndex - startIndex;// 列数据行索引
 20         foreach (string colName in columnsData.Keys)
 21         {
 22             object cell = m_SourceDataTable.Rows[rowIndex][colName];
 23             string cellStr = (cell + "").TrimEnd(new char[] { '', ' ' });
 24             if (colName == "COL3" || colName == "COL5")
 25             {
 26                 decimal value = 0;
 27                 decimal.TryParse(cellStr, out value);
 28                 ((decimal[])columnsData[colName])[dicRowIndex] = value;
 29             }
 30             else
 31             {
 32                 ((string[])columnsData[colName])[dicRowIndex] = cellStr;
 33             }
 34         }
 35     }
 36     m_DAL.InsertBigData(columnsData, currDataCount);
 37 
 38     columnsData.Clear();
 39     GC.Collect();
 40     GC.WaitForFullGCComplete();
 41 }

View Code

逐条数据插入INSERT

数据导入的最简单方法就是编写 INSERT
语句,将数据逐条插入数据库。这种方法只适合导入少量数据,如 SQL*Plus
脚本创建某个表的种子数据。该方法的最大缺点就是导入速度缓慢,占用了大量的
CPU
处理时间,不适合大批量数据的导入;而其主要优点就是导入构思简单又有修改完善的弹性,不需要多做其它的准备就可以使用。如果你有很多时间没法打发,又想折磨一下数据库和
CPU,那这种方法正适合你。

www.9778.com,方式二:OracleBulkCopy

说明:

  1. OracleBulkCopy 采用 direct path 方式导入;

  2. 不支持 transaction,无法 Rollback;

  3. 如果该表存在触发器时,无法使用 OracleBulkCopy(报异常信息 Oracle
    Error: ORA-26086),除非先禁用该表的所有触发器;

  4. 过程中会自动启用 NOT NULL、UNIQUE 和 PRIMARY KEY 三种约束,其中 NOT
    NULL 约束在列数组绑定时验证,任何违反 NOT NULL
    约束条件的行数据都会舍弃;UNIQUE
    约束是在导入完成后重建索引时验证,但是在 bulk copy
    时,允许违反索引约束,并在完成后将索引设置成禁用(UNUSABLE)状态;而且,如果索引一开始状态就是禁用(UNUSABLE)状态时,OracleBulkCopy
    是会报错的。

参考代码如下:

  1 /// <summary>
  2 /// 批量插入数据
  3 /// 该方法需要禁用该表所有触发器,并且插入的数据如果为空,是不会采用默认值
  4 /// </summary>
  5 /// <param name="table">数据表</param>
  6 /// <param name="targetTableName">数据库目标表名</param>
  7 /// <returns></returns>
  8 public bool InsertBulkData(DataTable table, string targetTableName)
  9 {
 10     bool result = false;
 11     string connStr = GetConnectionString();
 12     using (OracleConnection connection = new OracleConnection(connStr))
 13     {
 14         using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connStr, OracleBulkCopyOptions.Default))
 15         {
 16             if (table != null && table.Rows.Count > 0)
 17             {
 18                 bulkCopy.DestinationTableName = targetTableName;
 19                 for (int i = 0; i < table.Columns.Count; i++)
 20                 {
 21                     string col = table.Columns[i].ColumnName;
 22                     bulkCopy.ColumnMappings.Add(col, col);
 23                 }
 24                 connection.Open();
 25                 bulkCopy.WriteToServer(table);
 26                 result = true;
 27             }
 28             bulkCopy.Close();
 29             bulkCopy.Dispose();
 30         }
 31     }
 32 
 33     return result;
 34 }

测试结果:

数据类型:4列NVARCHAR2,2列NUMBER

30+万(7.36M):用时 14:590

60+万(14.6M):用时 28:28

1048576(24.9M):用时 52:971

附加,禁用表的所有外键SQL:

ALTER TABLE table_name DISABLE ALL TRIGGERS

数据导入的最简单方法就是编写 INSERT
语句,将数据逐条插入数据库。这种方法只适合导入少量数据,如 SQL*Plus
脚本创建某个表的种子数据。该方法的最大缺点就是导入速度缓慢,占用了大量的
CPU
处理时间,不适合大批量数据的导入;而其主要优点就是导入构思简单又有修改完善的弹性,不需要多做其它的准备就可以使用。如果你有很多时间没法打发,又想折磨一下数据库和
CPU,那这种方法正适合你。

为了与其它方法做比较,现将十万条记录通过此方法导入到 CALLS
表中,总共消耗 172 秒,其中导入进程占用 CPU 时间为 52 秒。

总结

1、在30+万和60+万数据时,ArrayBind一次性导入和OracleBulkCopy时间相差不是很大,但是ArrayBind方式一般都需要转换数据形式,占用了一些时间,而
OracleBulkCopy 则只需要简单处理一下 DataTable 数据源即可导入;

2、当数据量达到100+万时,ArrayBind
很容易出现内存不足异常,此时只能采用分批次执行导入,根据测试结果可知,次数越少,速度越快;而采用
OracleBulkCopy 方式则很少出现内存不足现象,由此可见 OracleBulkCopy
占用内存比 ArrayBind 方式少;

3、采用 OracleBulkCopy 导入时,先要禁用该表所有触发器,如果该表存在自增
ID 触发器,就比较麻烦了,得先禁用改变的自增 ID
的触发器,然后手动自增设置要导入的数据,最后才可以导入;同时,这种导入方式不可并发,一个时刻只能有一个用户在导入(因为自增ID交由程序处理),此时还需要锁表,防止其他人同时批量导入数据;

为了与其它方法做比较,现将十万条记录通过此方法导入到 CALLS
表中,总共消耗 172 秒,其中导入进程占用 CPU 时间为 52 秒。

逐条数据插入 INSERT,表暂无索引

参考资料:

1、ArrayBind

2、ArrayBind

3、Oracle数据导入方法

4、介绍OracleBulkCopy类

5、

逐条数据插入 INSERT,表暂无索引

为什么上一种方法占用了较多的 CPU 处理时间,关键是 CALLS
表中已创建了索引,当一条数据插入到表中时,Oracle
需要判别新数据与老数据在索引方面是否有冲突,同时要更新表中的所有索引,重复更新索引会消耗一定的时间。因此提高导入速度的好办法就是在创建表时先不创建索引或者在导入数据之前删除所有索引,在外部文件数据逐条插入到表中后再统一创建表的索引。这样导入速度会提高,同时创建的索引也很紧凑而有效,这一原则同样适用于位图索引。对于主要的和唯一的关键约束(key
constraints),可以使之先暂时失效(disabling)或者删除约束来获得同样的效果,当然这些做法会对已经存在的表的外键约束产生相关的影响,在删除前需要通盘斟酌。

为什么上一种方法占用了较多的 CPU 处理时间,关键是 CALLS
表中已创建了索引,当一条数据插入到表中时,Oracle
需要判别新数据与老数据在索引方面是否有冲突,同时要更新表中的所有索引,重复更新索引会消耗一定的时间。因此提高导入速度的好办法就是在创建表时先不创建索引或者在导入数据之前删除所有索引,在外部文件数据逐条插入到表中后再统一创建表的索引。这样导入速度会提高,同时创建的索引也很紧凑而有效,这一原则同样适用于位图索引。对于主要的和唯一的关键约束(key
constraints),可以使之先暂时失效(disabling)或者删除约束来获得同样的效果,当然这些做法会对已经存在的表的外键约束产生相关的影响,在删除前需要通盘斟酌。

需要说明的是,这种方法在表中已存在很多数据的情况下不太合适。例如表中已有九千万条数据,而此时需要追加插入一千万条数据,实际导入数据节省的时间将会被重新创建一亿条数据的索引所消耗殆尽,这是我们不希望得到的结果。但是,如果要导入数据的表是空的或导入的数据量比已有的数据量要大得多,那么导入数据节省的时间将会少量用于重新创建索引,这时该方法才可以考虑使用。
加快索引创建是另一个需要考虑的问题。为了减少索引创建中排序的工作时间,可以在当前会话中增加
SORT_AREA_SIZE
参数的大小,该参数允许当前会话在内存的索引创建过程中执行更多的排序操作。同样还可以使用
NOLOGGING 关键字来减少因创建索引而生成的 REDO 日志量,NOLOGGING
关键字会对数据库的恢复和 Standby
备用数据库产生明显的影响,所以在使用之前要仔细斟酌,到底是速度优先还是稳定优先。

需要说明的是,这种方法在表中已存在很多数据的情况下不太合适。例如表中已有九千万条数据,而此时需要追加插入一千万条数据,实际导入数据节省的时间将会被重新创建一亿条数据的索引所消耗殆尽,这是我们不希望得到的结果。但是,如果要导入数据的表是空的或导入的数据量比已有的数据量要大得多,那么导入数据节省的时间将会少量用于重新创建索引,这时该方法才可以考虑使用。
加快索引创建是另一个需要考虑的问题。为了减少索引创建中排序的工作时间,可以在当前会话中增加
SORT_AREA_SIZE
参数的大小,该参数允许当前会话在内存的索引创建过程中执行更多的排序操作。同样还可以使用
NOLOGGING 关键字来减少因创建索引而生成的 REDO 日志量,NOLOGGING
关键字会对数据库的恢复和 Standby
备用数据库产生明显的影响,所以在使用之前要仔细斟酌,到底是速度优先还是稳定优先。

运用这种方法,先删除 CALLS
表的主键和不唯一的索引,然后逐条导入数据,完成后重新创建索引(
表在导入数据前是空的)。该方法总共消耗 130
秒,包括重建索引的时间,其中导入进程占用 CPU 时间为 35秒。

运用这种方法,先删除 CALLS
表的主键和不唯一的索引,然后逐条导入数据,完成后重新创建索引(
表在导入数据前是空的)。该方法总共消耗 130
秒,包括重建索引的时间,其中导入进程占用 CPU 时间为 35秒。

这种方法的优点是可以加快导入的速度并使索引更加紧凑有效;缺点是缺乏通用性,当你对表增加新的复杂的模式元素时你需要添加代码、修改导入执行程序。另外针对
7*24
在线要求的数据库在线导入操作时,删除表的索引会对在线用户的查询有很大的性能影响,同时也要考虑,主要或唯一的关键约束条件的删除或失效可能会影响到引用它们的外键的使用。

这种方法的优点是可以加快导入的速度并使索引更加紧凑有效;缺点是缺乏通用性,当你对表增加新的复杂的模式元素时你需要添加代码、修改导入执行程序。另外针对
7*24
在线要求的数据库在线导入操作时,删除表的索引会对在线用户的查询有很大的性能影响,同时也要考虑,主要或唯一的关键约束条件的删除或失效可能会影响到引用它们的外键的使用。

批量插入,表暂无索引

批量插入,表暂无索引

在Oracle V6 中 OCI
编程接口加入了数组接口特性。数组操作允许导入程序读取外部文件数据并解析后,向数据库提交SQL语句,批量插入
SQL 语句检索出的数据。Oracle 仅需要执行一次 SQL
语句,然后在内存中批量解析提供的数据。批量导入操作比逐行插入重复操作更有效率,这是因为只需一次解析
SQL
语句,一些数据绑订操作以及程序与数据库之间来回的操作都显著减少,而且数据库对每一条数据的操作都是重复可知的,这给数据库提供了优化执行的可能。其优点是数据导入的总体时间明显减少,特别是进程占用
CPU 的时间。

在Oracle V6 中 OCI
编程接口加入了数组接口特性。数组操作允许导入程序读取外部文件数据并解析后,向数据库提交SQL语句,批量插入
SQL 语句检索出的数据。Oracle 仅需要执行一次 SQL
语句,然后在内存中批量解析提供的数据。批量导入操作比逐行插入重复操作更有效率,这是因为只需一次解析
SQL
语句,一些数据绑订操作以及程序与数据库之间来回的操作都显著减少,而且数据库对每一条数据的操作都是重复可知的,这给数据库提供了优化执行的可能。其优点是数据导入的总体时间明显减少,特别是进程占用
CPU 的时间。

需要提醒的是,通过 OCI
接口确实可以执行数据批量导入操作,但是许多工具和脚本语言却不支持使用此功能。如果要使用该方法,需要研究你所使用的开发工具是否支持
OCI
批量操作功能。导入程序需要进行复杂的编码并可能存在错误的风险,缺乏一定的弹性。

需要提醒的是,通过 OCI
接口确实可以执行数据批量导入操作,但是许多工具和脚本语言却不支持使用此功能。如果要使用该方法,需要研究你所使用的开发工具是否支持
OCI
批量操作功能。导入程序需要进行复杂的编码并可能存在错误的风险,缺乏一定的弹性。

运用上述方法,程序将外部数据提取到内存中的数组里,并执行批量插入操作,保留了表的删除/重建索引操作,总的导入时间下降到
14 秒,而进程占用 CPU
的时间下降到7秒,可见实际导入数据所花费的时间显著下降了 95%。

运用上述方法,程序将外部数据提取到内存中的数组里,并执行批量插入操作,保留了表的删除/重建索引操作,总的导入时间下降到
14 秒,而进程占用 CPU
的时间下降到7秒,可见实际导入数据所花费的时间显著下降了 95%。