如何通过DBLINK通过DBLINK调用SELECT? [英] 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

• create database link to C
• create synonym table for table@C

然后在A上

SELECT *
  FROM table@B

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