sql-server - SSIS，使用来自 SQL Server 的 ID 查询 Oracle 表？

Question

这是我想在 SSIS 中做的事情的基本想法:

我有一个针对生产 Oracle 数据库的大型查询，我需要以下 where 子句从 SQL Server 引入一长串 ID。结果从那里发送到其他地方。

select ... 
from Oracle_table(s) --multi-join
where id in ([select distinct id from SQL_SERVER_table])

或者，我可以这样写查询:

select ...
from Oracle_table(s) --multi-join
...
join SQL_SERVER_table sst on sst.ID = Oracle_table.ID

这是我的限制:

• Oracle 查询很大，没有 where id in (... 子句就无法运行
  • 这意味着我无法运行 Oracle 查询，然后在另一步骤中将其与 ID 结合起来。我试过了，DBA 在临时表的大小变为 3 TB 后将其终止。
• 我有 16 万个 ID
  • 这意味着逐个遍历 ID 是不切实际的。过去，我使用逗号分隔的列表对大约 1000 个 ID 进行过测试。它运行得相对较快 - 几分钟。
• 主要查询在 Oracle 中，但 ID 在 SQL Server 中
• 我没有写入 Oracle 的能力

我发现了很多这样的问题。
我找到的所有答案都无法解决我的局限性。

类似问题:

• Query a database based on result of query from another database

Answer 1

防止从 Oracle 表中加载所有行。唯一的方法是在 Oracle 数据库引擎中应用过滤器。我不认为这可以使用 SSIS 来实现，因为 SQL Server 表中有超过 160000 个 ID，无法有效地加载并传递给 Oracle SQL 命令:

• 使用 Lookups 和 Merge Join 需要从 Oracle 数据库加载所有数据
• 从 SQL Server 检索数据、构建逗号分隔的字符串并将其传递给 Oracle SQL 命令不能使用过多的 ID (160K)。
• 使用脚本任务同样的问题。
• 在 SQL Server 中创建链接服务器并连接两个表将从 Oracle 数据库加载所有数据。

要解决您的问题，您应该寻找一种方法来创建从 Oracle 引擎到 SQL Server 数据库的链接。

Oracle 异构服务

我在 Oracle 数据库方面没有太多经验。尽管如此，经过一些小的研究，我在 Oracle 中发现了与 SQL Server 中的“链接服务器”等效的东西，称为“异构连接”。

查询语法应该是这样的:

select * 
from Oracle_table
where id in (select distinct id from SQL_SERVER_table@sqlserverdsn)

您可以引用以下分步指南，详细了解如何从 Oracle 连接到 SQL Server 表:

• What is Oracle equivalent for Linked Server and can you join with SQL Server?
• Making a Connection from Oracle to SQL Server - 1
• Making a Connection from Oracle to SQL Server - 2
• Heterogeneous Database connections - Oracle to SQL Server

将数据从 SQL Server 导入到 Oracle 中的暂存表

另一种方法是使用数据流任务将 ID 从 SQL Server 导入到 Oracle 中的暂存表。然后在 Oracle 查询中使用暂存表。最好在临时表上创建一个索引。 (如果您没有写入 Oracle 数据库的权限，请尝试获取对单独的暂存数据库的权限。)

将数据从 SQL Server 导出到 Oracle 的示例:

• Export SQL Server Data to Oracle using SSIS

最小化 Oracle 表的数据加载

如果以上解决方案都不能解决您的问题。可以尝试尽量减少从Oracle数据库加载的数据。

例如，您可以尝试从 SQL Server 表中获取最小和最大 ID，将这两个值存储在两个变量中。然后，您可以在从 Oracle 表加载数据的 SQL 命令中使用这两个变量，如下所示:

SELECT * FROM Oracle_Table WHERE ID > @MinID and ID < @MaxID

这将在您的操作中删除一堆无用的数据。如果您的 ID 列是字符串，您可以使用其他措施来过滤数据，例如字符串长度、第一个字符。