Select and Insert across dblink
I am having a bit of trouble with a select into insert across a dblink in oracle 10. I am using the following statement:
INSERT INTO LOCAL.TABLE_1 ( COL1, COL2)
SELECT COL1, COL2
FROM R开发者_StackOverflow社区EMOTE.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:
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?
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
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.
Leveraging the WITH clause could optimize your retrieval of your working set:
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
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.
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.
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;
/
Insert into zith cardinality hint seems to work in 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
精彩评论