时间戳

首页 > 解决方案 > 强制 Oracle 在远程数据库站点上处理递归 CTE（可能使用 DRIVING_SITE 提示）

sql - 强制 Oracle 在远程数据库站点上处理递归 CTE（可能使用 DRIVING_SITE 提示）

问题描述

我正在尝试从远程表中获取数据。使用递归 CTE 从本地表中的种子数据集扩展数据。查询非常慢（从 300 个种子行到 800 个最终行需要 7 分钟）。

对于其他没有递归查询的“微小的本地，巨大的远程”DRIVING_SITE情况，提示效果很好。我还尝试将种子集从本地表导出到remotedb具有相同结构的辅助表中，并且 - 正在登录remotedb- 将查询作为纯本地查询（my_tableas p，my_table_seed_copyas i）运行。花了 4 秒，这让我相信强制查询到远程站点会使查询速度更快。

强制Oracle在远程站点上执行递归查询的正确方法是什么？

with s (id, data) as (
  select p.id, p.data
  from my_table@remotedb p
  where p.id in (select i.id from my_table i)
  union all
  select p.id, p.data
  from s
  join my_table@remotedb p on ...
)
select /*+DRIVING_SITE(p)*/ s.*
from s;

在上面的查询中，我尝试了

均未成功（即 7 分钟后返回数据）。

标签： sqloracleoracle11grecursive-querydatabase-link

解决方案

递归查询实际上执行广度优先搜索 - 种子行表示第 0 级，递归部分从(n-1)级的元素中查找第n级的元素。原始查询旨在成为子句的一部分。merge ... using ...

因此，我将查询重写为 PLSQL 循环。每个循环生成一个级别。合并可防止插入重复项，因此最终不会添加新行并退出循环（构造传递闭包）。伪代码：

loop
  merge into my_table using (
    select /*+DRIVING_SITE(r)*/ distinct r.* /*###BULKCOLLECT###*/
    from my_table          l
    join my_table@remotedb r on ...  -- same condition as s and p in original question are joined on
  ) ...
  exit when rows_inserted = 0;
end loop;

实际代码并不那么简单，因为DRIVING_SITE实际上并不直接使用，merge所以我们必须通过工作集合传输数据，但那是另一回事。此外，插入的行数也不容易确定，它必须计算为合并前后的行数之差。

解决方案并不理想。无论如何，它比递归 CTE（30 秒，13 个周期）要快得多，因为查询可证明利用了DRIVING_SITE提示。

如果有人找到答案如何使递归查询工作或证明它是不可能的，我会将问题留待一段时间等待。

推荐阅读