oracle - 如何高效地从 SAS 更新 Oracle 表？

Question

我试图解决的问题:

我有一个 SAS 数据集 work.testData(在工作库中)，其中包含 8 列和大约 100 万行。所有列均为文本(即没有数字数据)。该 SAS 数据集的文件大小约为 100 MB。我的目标是有一个步骤将整个 SAS 数据集解析到 Oracle 中。即有点像将 SAS 数据集从 SAS 平台“复制并粘贴”到 Oracle 平台。其背后的基本原理是，Oracle 中的该表每天都会被 SAS 中的表“替换”，从而启用下游 Oracle 流程。

我解决问题的方法:

Oracle 中的一次性初始设置:

1. 在 Oracle 中，我创建了一个名为 testData 的表，其表结构与 SAS 数据集 testData 几乎相同。 (即相同的表名、相同的列数、相同的列名等)。

持续重复过程:

在 SAS 中，执行 SQL 传递以截断 ora.testData(即删除所有行，同时保留表结构)。这可确保在从 SAS 插入之前 ora.testData 为空。

在 SAS 中，LIBNAME 语句将 Oracle 数据库指定为 SAS 库(称为 ora)。这样我就可以“查看”Oracle 中的内容并从 SAS 执行读取/更新。

在 SAS 中，PROC SQL 过程用于将 SAS 数据集 work.testData 中的数据“插入”到 Oracle 表 ora.testData 中。

示例代码

Oracle 中的一次性初始设置:

第 1 步:在 Oracle SQL Developer 中运行此 Oracle SQL 脚本(为表 testData 创建表结构。从 0 行数据开始。)

DROP TABLE testData;
CREATE TABLE testData
  (
    NODENAME          VARCHAR2(64) NOT NULL,
    STORAGE_NAME      VARCHAR2(100) NOT NULL,
    TS                VARCHAR2(10) NOT NULL,
    STORAGE_TYPE      VARCHAR2(12) NOT NULL,
    CAPACITY_MB       VARCHAR2(11) NOT NULL,
    MAX_UTIL_PCT      VARCHAR2(12) NOT NULL,
    AVG_UTIL_PCT      VARCHAR2(12) NOT NULL,
    JOBRUN_START_TIME VARCHAR2(19) NOT NULL
  )
;
COMMIT;

持续重复过程:

第 2、3 和 4 步:在 SAS 中运行此 SAS 代码

******************************************************;
******* On-going repeatable process starts here ******;
******************************************************;

*** Step 2: Trancate the temporary Oracle transaction dataset;
proc sql;
  connect to oracle (user=XXX password=YYY path=ZZZ);
  execute (
    truncate table testData
  ) by oracle;
  execute (
    commit
  ) by oracle;
  disconnect from oracle;
quit;

*** Step 3: Assign Oracle DB as a libname;
LIBNAME ora Oracle user=XXX password=YYY path=ZZZ dbcommit=100000;

*** Step 4: Insert data from SAS to Oracle;
PROC SQL; 
  insert into ora.testData
  select NODENAME length=64,
         STORAGE_NAME length=100,
         TS length=10,
         STORAGE_TYPE length=12,
         CAPACITY_MB length=11,
         MAX_UTIL_PCT length=12,
         AVG_UTIL_PCT length=12,
         JOBRUN_START_TIME length=19
  from work.testData; 
QUIT;

******************************************************;
**** On-going repeatable process ends here       *****;
******************************************************;

我的方法的局限性/问题:

Proc SQL 步骤(将 100 MB 数据从 SAS 传输到 Oracle)大约需要 5 个小时才能执行 - 该作业运行时间太长!

问题:

是否有更明智的方法来执行从 SAS 到 Oracle 的数据传输？ (即从 SAS 更新 Oracle 表)。

Answer 1

首先，如果有必要，您可以从 SAS 中删除/重新创建。我不会每次都删除并重新创建 - 截断似乎更容易获得相同的结果 - 但如果你有其他原因，那也没关系；但无论哪种方式，您都可以使用execute (truncate table xyz) from oracle或类似于drop，使用直通连接。

其次，假设表上没有约束或索引(考虑到您正在删除并重新创建它，这似乎很可能)，您可能无法改进这一点，因为它可能基于网络延迟。但是，您应该在连接设置(您未提供)中查看一个区域:SAS 提交数据的频率。

有两种方法可以控制它，DBCOMMMIT设置和 BULKLOAD环境。前者控制执行提交的频率(因此，如果DBCOMMIT=100，则每 100 行执行一次提交)。更频繁的提交 = 如果发生随机故障，丢失的数据更少，但执行速度更慢。对于 PROC SQL INSERT，DBCOMMIT 默认为 0，这意味着仅进行一次提交(假设没有错误的最快选项)，因此除非您覆盖它，否则这不太可能有帮助。

Bulkload 可能是我的建议；它使用 SQLLDR 加载您的数据，即，它将整个位批量传输到 Oracle，然后说“请加载此数据，谢谢。”它仅适用于某些设置和某些类型的查询，但它应该在这里工作(受其他条件限制 - 请阅读上面的文档页面)。

如果您使用的是 BULKLOAD，那么您可能会遇到网络延迟的问题。 5 小时 100 MB 似乎很慢，但我在(相对较短的)一天里看到了各种各样的东西。如果 BULKLOAD 不起作用，我可能会邀请 Oracle DBA 并让他们从 .csv 文件和 SQL*LDR 命令文件开始解决此问题(这应该与 SAS 使用 BULKLOAD 所做的基本相同)；他们应该知道如何解决该问题，并且至少能够监控数据库本身的性能。如果这里存在问题的其他表存在约束(即，其他表根据您的插入或其他内容频繁地重新计算自身)，他们应该能够找到并推荐解决方案。

您可以查看PROC DBLOAD，它有时比 SQL 中的插入更快(尽管总而言之不应该如此，并且是一个不再使用太多的“较旧”过程)。您还可以研究是否可以避免进行完全刷新和填充(即，是否有办法通过网络传输更少的数据)，或者甚至简单地缩小列大小。