oracle 跨dblink选择和插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2462901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select and Insert across dblink
提问by Domtar
I am having a bit of trouble with a select into insert across a dblink in oracle 10. I am using the following statement:
我在 oracle 10 中的 dblink 中选择插入时遇到了一些麻烦。我使用以下语句：
INSERT INTO LOCAL.TABLE_1 ( COL1, COL2)
SELECT COL1, COL2
FROM REMOTE.TABLE1@dblink s
WHERE COL1 IN ( SELECT COL1 FROM WORKING_TABLE)
When I run the statement the following is what gets run against the remote server on the DB Link:
当我运行该语句时，以下是针对 DB Link 上的远程服务器运行的内容：
SELECT /*+ OPAQUE_TRANSFORM */ "COL1", "COL2"
FROM "REMOTE"."TABLE1" "S"
If I run the select only and do not do the insert into the following is run:
如果我只运行选择并且不执行插入到以下运行：
SELECT /*+ */ "A1"."COL1"
, "A1"."COL2"
FROM "REMOTE"."TABLE1" "A1"
WHERE "A1"."COL1" =
ANY ( SELECT "A2"."COL1"
FROM "LOCAL"."TABLE1"@! "A2")
The issue is in the insert case the enitre table is being pulled across the dblink and then limited localy which takes a fair bit of time given the table size. Is there any reason adding the insert would change the behavior in this manner?
问题是在插入情况下，整个表被拉过 dblink，然后限制本地，这需要相当长的时间给定表大小。是否有任何理由添加插入会以这种方式改变行为？
回答by Rene
You may want to use the driving_site hint. There is a good explanation here: http://www.dba-oracle.com/t_sql_dblink_performance.htm
您可能想要使用driving_site 提示。这里有一个很好的解释：http: //www.dba-oracle.com/t_sql_dblink_performance.htm
回答by Rene
When it comes to DML, oracle chooses to ignore any driving_site hint and executes the statement at the target site. So I doubt if you would be able to change that (even using WITH approach described above). A possible workaround is you can create a synonym for LOCAL.TABLE1 on the remote database and use the same in your INSERT statement.
当涉及到 DML 时，oracle 选择忽略任何驱动站点提示并在目标站点执行语句。所以我怀疑你是否能够改变它（即使使用上面描述的 WITH 方法）。一种可能的解决方法是您可以在远程数据库上为 LOCAL.TABLE1 创建同义词，并在您的 INSERT 语句中使用它。
回答by T.j.
Leveraging the WITH clause could optimize your retrieval of your working set:
利用 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
回答by NMR
Oracle will ignore the driving_site hint for insert statements, as DML is always executed locally. The way to do this is to create a cursor with the driving site hint, and then loop through the cursor with a bulkcollect/forall and insert into the target local table.
Oracle 将忽略插入语句的驱动站点提示，因为 DML 始终在本地执行。这样做的方法是创建一个带有驱动站点提示的游标，然后用一个bulkcollect/forall 遍历游标并插入到目标本地表中。
回答by Gary Myers
How big is WORKING_TABLE ? If it is small enough, you could try selecting from work_table into a collection, and then passing the elements of that collect as elements in an IN list.
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;
/
回答by Lavallee Alain
Insert into zith cardinality hint seems to work in 11.2
插入 zith 基数提示似乎在 11.2 中有效
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
