如何通过 DBLINK 调用 SELECT over DBLINK? [英] How to invoke SELECT over DBLINK over DBLINK?

问题描述

在 Oracle 11G 中，我可以轻松调用:

In Oracle 11G I can easily invoke:

SELECT * FROM TABLE@DB_LINK_NAME;

但是如何通过另一个 DB_LINK 上的 DB_LINK 调用 SELECT?

But how invoke SELECT over DB_LINK that is on another DB_LINK?

像这样:

SELECT * FROM TABLE@REMOTE_DB_LINK_NAME@DB_LINK_NAME;

推荐答案

首先，在架构上，我对任何涉及通过多个数据库链接提取数据的设计都持谨慎态度.当最终源是目标数据库无法直接连接的某个古老版本的 Oracle 时，我已经看到它完成了，因此使用了运行中间版本 Oracle 的中间数据库.不过，这在实践中非常罕见.

First off, architecturally, I'd be pretty leery of any design that involved pulling data over multiple database links. I've seen it done when the eventual source is some ancient version of Oracle that the target database cannot connect to directly so an intermediate database running an intermediate version of Oracle was used. That is very rare in practice, though.

从性能的角度来看，这种方法存在严重问题.当然，存在数据将通过网络发送两次的问题.但更令人担忧的是，您正在解决一个难题，优化分布式 SQL 语句，使其几乎变得棘手.您基本上必须要么保证永远不会在同一个查询中查询本地数据和远程数据，要么如果 Oracle 决定采用愚蠢的查询计划，您将不得不忍受由此产生的性能，因为剩下的工具集允许您优化这种查询是最小的.

From a performance perspective, this sort of approach is gravely problematic. There is, of course, the issue that the data is going to be sent over the network twice. But more to worryingly, you are taking a difficult problem, optimizing distributed SQL statements, and making it nearly intractable. You'd basically have to either guarantee that you would never query local data and remote data in the same query or you would have to live with the resulting performance if Oracle decides on a stupid query plan because the set of tools left to allow you to optimize this sort of query is minimal.

话虽如此，中间数据库需要具有抽象出数据库链接的同义词或视图.所以

That being said, the intermediate database would need to have synonyms or views that abstract away the database link. So

在 A:

• 创建到 B 的数据库链接

在 B 上:

• 创建到 C 的数据库链接
• 为table@C
创建同义词table

在 A 上，你可以

SELECT *
  FROM table@B

这篇关于如何通过 DBLINK 调用 SELECT over DBLINK?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！