跨数据库连接选择和插入

Question

我在使用Oracle 10中的跨数据库链接(select into insert across a dblink)时遇到了一些麻烦。 我正在使用以下语句：

INSERT INTO LOCAL.TABLE_1 ( COL1, COL2) 
SELECT  COL1, COL2
FROM REMOTE.TABLE1@dblink s
WHERE COL1 IN ( SELECT COL1 FROM WORKING_TABLE)

当我运行该语句时，在DB链接上远程服务器运行以下内容:

SELECT /*+ OPAQUE_TRANSFORM */ "COL1", "COL2"
FROM "REMOTE"."TABLE1" "S"

如果我只运行查询而不进行以下的插入，则会运行：

SELECT /*+ */ "A1"."COL1"
     , "A1"."COL2"
  FROM "REMOTE"."TABLE1" "A1"
 WHERE "A1"."COL1" =
   ANY ( SELECT "A2"."COL1"
       FROM "LOCAL"."TABLE1"@! "A2")

问题在于，在插入数据的情况下，整个表格都将通过dblink拉取，然后在本地进行限制，这需要相当长的时间，考虑到表格的大小。是否有任何原因使添加插入会改变这种行为方式？

- Domtar

Answer 1

您可能希望使用 driving_site 提示。这里有一个很好的解释： http://www.dba-oracle.com/t_sql_dblink_performance.htm

- Rene

---

我在发布之前尝试将以下提示添加到初始查询中，但结果相同。/+DRIVING_SITE(s)/这是提示在dblink端进行驱动操作，对吗？ - Domtar

Answer 2

当涉及到DML时，Oracle选择忽略任何driving_site提示，并在目标站点上执行语句。因此，我怀疑您是否能够更改它（即使使用上面描述的WITH方法）。一个可能的解决方法是，在远程数据库上为LOCAL.TABLE1创建一个同义词，并在INSERT语句中使用相同的表名。

- Narendra

Answer 3

利用WITH子句可以优化您的工作集检索：

WITH remote_rows AS
     (SELECT /*+DRIVING_SITE(s)*/COL1, COL2
      FROM REMOTE.TABLE1@dblink s
      WHERE COL1 IN ( SELECT COL1 FROM WORKING_TABLE)) 
INSERT INTO LOCAL.TABLE_1 ( COL1, COL2)
SELECT  COL1, COL2
FROM remote_rows

- T.j.

---

这个答案表明你的语法不正确。它是关于Oracle SQL中使用WITH子句进行INSERT INTO操作的问题。 - Alfabravo

Answer 4

对于插入语句，Oracle将忽略driving_site提示，因为DML始终在本地执行。解决方法是创建一个带有驱动站点提示的游标，然后通过bulkcollect/forall循环遍历游标并插入目标本地表。

- NMR

Answer 5

WORKING_TABLE 有多大？ 如果足够小，您可以尝试从 work_table 中选择到一个集合中，然后将该集合的元素作为 IN 列表中的元素传递。

declare
  TYPE t_type IS TABLE OF VARCHAR2(60);
  v_coll t_type;
begin
  dbms_application_info.set_module('TEST','TEST');
  --
  select distinct object_type 
  bulk collect into v_coll
  from user_objects;
  --
  IF v_coll.count > 20 THEN
    raise_application_error(-20001,'You need '||v_coll.count||' elements in the IN list');
  ELSE
    v_coll.extend(20);
  END IF;
  insert into abc (object_type, object_name)
  select object_type, object_name
  from user_objects@tmfprd
  where object_type in 
            (v_coll(1), v_coll(2), v_coll(3), v_coll(4), v_coll(5), 
            v_coll(6), v_coll(7), v_coll(8), v_coll(9), v_coll(10),
            v_coll(11), v_coll(12), v_coll(13), v_coll(14), v_coll(15), 
            v_coll(16), v_coll(17), v_coll(18), v_coll(19), v_coll(20)
             );
  --
  dbms_output.put_line(sql%rowcount);
end;
/

- Gary Myers

---

那是我的最初方法，但工作表中可能有超过1000个元素。我认为我必须将处理分成每组1000个，以保持在where in限制范围内。 - Domtar

你能否在远程链接的一侧创建全局临时表？你可以从工作表中插入数据到该表中，然后连接将全部在远程一侧进行。 - Gary Myers

Answer 6

在11.2版本中，插入到zith基数提示似乎是有效的。

 INSERT /*+ append */  
        INTO MIG_CGD30_TEST       
                SELECT  /*+ cardinality(ZFD 400000) cardinality(CGD 60000000)*/ 
            TRIM (CGD.NUMCPT) AS NUMCPT, TRIM (ZFD.NUMBDC_NEW) AS NUMBDC
              FROM CGD30@DBL_MIG_THALER CGD,
                   ZFD10@DBL_MIG_THALER ZFD,
                   EVD01_ADS_DR3W2  EVD

- Lavallee Alain