将来自 Oracle 和 SQL Server 的数百万条记录进行比较。

Question

我有一个Oracle数据库和一个SQL Server数据库。有一张表叫做Inventory，在这两个数据库表中都包含着数以百万计的行，并且它们在不断增长。

我想要比较Oracle表数据和SQL Server数据，以便每天找出SQL Server表中缺失哪些记录。

哪种方法最好呢？

1. 创建SSIS包。
2. 创建Windows服务。

我希望使用更少的资源来实现此功能，耗时和资源消耗都很少。

例如：Oracle中有1800万条记录，而SQL Server中只有1600万或1700万条记录。

这种两个不同的数据库的情况是因为有两个不同的应用程序在在线和离线工作。 编辑：如何通过Oracle网关连接到SQL Server：

1. 直接从Oracle查询SQL Server，更新SQL Server中缺失的记录。

2. 在Oracle上创建触发器，当从Oracle中删除记录时执行，将被删除的记录插入新的Oracle表中。

3. 创建SSIS包，将新创建的Oracle表映射到SQL Server，以更新SQL Server记录。这样每天只需要处理少量记录。

你认为这种方法怎么样？

- Mahajan344

---

我认为一个好的长期策略是不要在不同的关系型数据库中存储你的状态。你能否更新一下问题，解释一下你是如何遇到这个问题的？ - Tim Biegeleisen

你能解释一下目前记录是如何复制的吗？ - Giova

Oracle数据库有所有的记录，但有可能会从Oracle数据库中删除记录，因此我们需要更新SQL Server记录，这些记录已从Oracle数据库中删除。 - Mahajan344

所以你不仅想要比较，还想要插入（和更新？）更改。你可能想把这个加到你的问题中。 - Nick.McDermaid

是的，你的编辑涉及到我之前提到的差异复制。 - Nick.McDermaid

Answer 1

我会创建一个SSIS包并使用数据流/OLE DB数据源从Oracle表中加载数据。如果您有SQL Enterprise，则Attunity Connectors会更快一些。

然后，我会将SQL Server表中的关键字加载到Lookup转换中，在那里我会将2个源与关键字匹配，并将不匹配的行定向到单独的输出。

最后，我会将未匹配的行输出定向到OLE DB命令以更新SQL Server表。

这个SSIS包需要大量内存，但由于匹配是在内存中进行的，IO很少，所以它可能比其他解决方案更快。它需要足够的空闲内存来缓存来自SQL Server表的所有关键字。

SSIS还具有许多其他转换功能，如果以后需要，可以使用它们。

- Mike Honey

---

谢谢您的回复。那么您建议使用SSIS包和SSIS查找是比较1,800万条Oracle记录和15/16百万SQL Server记录的最佳方法吗？ - Mahajan344

是的，由于最小化IO。SSIS只需要从Oracle和SQL Server表中读取一次，然后就会在内存中进行匹配，而大多数其他技术需要读取和写入（然后再次读取）您的大型数据集之一。 - Mike Honey

我认为SSIS查找可能无法处理那么大的数据量（不过如果有需要更正的地方，我很乐意接受）。如果存在任何复杂度（例如大小写不敏感或范围查找），它必须逐行执行查找，这对于1800万条记录来说将非常缓慢。 - Nick.McDermaid

我想象一个典型的键匹配，例如所有列都是数字或可以作为区分大小写匹配，因此我将使用Lookup的默认Full Cache模式。正如我所提到的，这将需要足够的内存，例如18m x平均键长度。例如，如果键是单个int / DT_I4列，则为18m x 4字节= 72MB。 - Mike Honey

Answer 2

你想做的基本上是从Oracle到SQL Server的复制。您可以在SSIS、Windows服务或多种平台上完成此操作。真正的诀窍是使用正确的设计模式。有两种常见的设计模式:

1. 快照复制。您从两个系统中获取所有记录，并在某个地方进行比较（到目前为止，我们建议在SSIS中进行比较或在Oracle上进行比较，但尚未建议在SQL Server上进行比较，尽管这是有效的）。在这里，您正在比较1800万条记录，所以这是很多工作。

2. 差异复制。您记录自上次复制以来发布者（即Oracle）中的更改，然后将这些更改应用于订阅者（即SQL Server）。您可以通过在Oracle端实现触发器和日志表，然后使用常规ETL过程（SSIS、命令行工具、文本文件等）手动执行此操作，可能在SQL Agent中进行调度。

或者您可以使用开箱即用的复制功能，将Oracle设置为发布者，SQL设置为订阅者来实现此操作：https://msdn.microsoft.com/en-us/library/ms151149(v=sql.105).aspx

您需要尝试几种方法，看看哪种适合您。

考虑到这个目标：

我想消耗更少的资源来实现这个功能，需要更少的时间和资源

事务复制效率更高但较为复杂。出于维护目的，您最熟悉哪些平台（.Net、SSIS、Python等）？

- Nick.McDermaid

Answer 3

我认为最好的方法是使用Oracle Gateway。只需按照以下步骤操作。我有类似的经验。

1. 安装和配置Oracle Database Gateway for SQL Server。 https://docs.oracle.com/cd/B28359_01/gateways.111/b31042/installsql.htm
2. 现在您可以从Oracle到SQL Server创建一个dblink。
3. 创建一个过程，比较Oracle数据库中缺失的记录并插入到SQL Server数据库中。

例如，您可以在过程中使用此语句。

    INSERT INTO "dbo"."sql_server_table"@dblink_name("column1","column2"...."column5")
VALUES
(
    select column1,column2....column5 from oracle_table
    minus
   select "column1","column2"...."column5" from "dbo"."sql_server_table"@dblink_name
)

4. 创建一个调度程序，每天执行该过程。

当两个数据库都在线时，缺失的记录将被插入到SQL服务器中。否则，调度程序将失败或您可以手动执行该过程。它需要最少的资源。

- Md. Shamim Al Mamun

Answer 4

其他选择：

如果您可以使用Oracle网关用于SQL Server，则无需传输数据即可直接查询。

如果无法使用Oracle网关，您可以使用Pentaho数据集成或另一个ETL工具来比较表格并获取结果。非常易于使用。

- JLC

---

你的回答对我来说相当模糊，因为它没有给出任何实现这个目标的线索。同时也要考虑一个应用程序离线的情况。 - hotzst

嗨。我只是提出了一种与使用程序传输数据的替代方案。使用Oracle Gateway for SQLServer，您可以直接进行比较。ETL工具也是如此，但这种替代方案有所不同。 - JLC

Answer 5

我建议使用自制的ETL解决方案。

1. 安排一个 oracle job，将源表数据以每日方式（根据应用逻辑）导出到纯文本CSV格式。
2. 安排一个SQL-Server job（与第一个Oracle Job有一定延迟），读取此CSV文件并使用 BULK INSERT导入到 SQL Server 中的中间表。
3. SQL-Server Job 的最后一部分将读取中间表数据并执行逻辑（插入、更新目标表）。我建议再添加另一个表来存储此每日作业结果的报告。

- Mohsen Heydari