从 Oracle 数据链路合并时的优化 [英] Optimization when merging from Oracle datalink

问题描述

我正在尝试编写一个 Oracle 过程来将来自远程数据链路的数据合并到本地表中.单独的部分工作很快，但他们一起超时.这是我正在尝试的简化版本.

I am trying to write an Oracle procedure to merge data from a remote datalink into a local table. Individually the pieces work quickly, but together they time out. Here is a simplified version of what I am trying.

什么有效:

Select distinct ProjectID from Project where LastUpdated < (sysdate - 6/24);

--瞬间工作.

    Merge into project 
     using (select /*+DRIVING_SITE(remoteCompData)*/ 
            rp.projectID,
            rp.otherdata
            FROM Them.Remote_Data@DBLink rd
            WHERE rd.projectID in (1,2,3)) sourceData -- hardcoded IDs
    On (rd.projectID = project.projectID)

When matched...

-- 当 ID 被硬编码时，Merge 语句工作得很快

-- Merge statement works quickly when the IDs are hard coded

什么不起作用:结合上面的两个语句.

What doesn't work: Combining the two statements above.

    Merge into project 
     using (select /*+DRIVING_SITE(rd)*/ -- driving site helps when this piece is extracted from the larger statement
            rp.projectID,
            rp.otherdata
            FROM Them.Remote_Data@DBLink rd
            WHERE rd.projectID in --in statement that works quickly by itself.
               (Select distinct ProjectID from Project where LastUpdated < (sysdate - 6/24)) 
-- This select in the in clause one returns 10 rows. Its a test database.
    On (rd.projectID = project.projectID)
    )

When matched...

-- 当我在 SQL Developer 中运行这个语句时，这就是我在没有数据更新的情况下得到的连接到本地数据库.进程退出.断开与本地数据库的连接.

-- When I run this statement in SQL Developer, this is all that I get without the data updating Connecting to the database local. Process exited. Disconnecting from the database local.

我还尝试将 in 语句提取到 with 语句中，希望它能以不同的方式执行，但没有效果.

I also tried pulling out the in statement into a with statement hoping it would execute differently, but it had no effect.

任何追求路径的方向将不胜感激.谢谢.

Any direction for paths to pursue would be appreciated. Thanks.

推荐答案

我发现你的问题有同样的问题.是的，当查询包含在 merge 命令的 using 子句中时，查询中的提示将被忽略.

I found your question having same problem. Yes, the hint in query is ignored when the query is included into using clause of merge command.

在我的例子中，我创建了工作表，例如 w_remote_data，并将合并命令拆分为两个命令:(1) 填充工作表，(2) 调用 merge 命令 using 工作表.

In my case I created work table, say w_remote_data for your example, and splitted merge command into two commands: (1) fill the work table, (2) invoke merge command using work table.

陷阱是，我们不能简单地使用 create w_remote_data as select/*+DRIVING_SITE(rd)*/... 或 insert into w_remote_data select/*+DRIVING_SITE(rd)*/... 填充工作表.这两个命令都是有效的，但它们很慢——提示也不适用，所以我们不会解决这个问题.解决方案是在 PLSQL 中:使用中间集合在 using 子句中收集查询结果.请参见示例(为简单起见，我假设 w_remote_data 与 remote_data 具有相同的结构，否则我们必须定义自定义记录而不是 %rowtype):

The pitfall is, we cannot simply use neither of commands create w_remote_data as select /*+DRIVING_SITE(rd)*/ ... or insert into w_remote_data select /*+DRIVING_SITE(rd)*/ ... to fill the work table. Both of these commands are valid but they are slow - the hint does not apply too so we would not get rid of the problem. The solution is in PLSQL: collect result of query in using clause using intermediate collection. See example (for simplicity I assume w_remote_data has same structure as remote_data, otherwise we have to define custom record instead of %rowtype):

declare
  type ct is table of w_remote_data%rowtype;
  c ct;
  i pls_integer;
begin
  execute immediate 'truncate table w_remote_data';
  select /*+DRIVING_SITE(rd)*/ *
  bulk collect into c
  from Them.Remote_Data@DBLink rd ...;
  if c.count > 0 then
    forall i in c.first..c.last
      insert into w_remote_data values c(i);
  end if;
  merge into project p using (select * from w_remote_data) ...;
  execute immediate 'truncate table w_remote_data';
end;

我的案例是 ETL 脚本，我可以信赖它不会并行运行.否则我们将不得不处理临时(会话私有)表，如果它适用于它们，我没有尝试.

My case was ETL script where I could rely it won't run in parallel. Otherwise we would have to cope with temporary (session-private) tables, I didn't try if it works with them.

这篇关于从 Oracle 数据链路合并时的优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！