SELECT xxxx
FROM (
SELECT DISTINCT FIELD1_CONTENT
FROM xxxxxxxx_LOG@DBLINK
WHERE UPPER(TABLENAME) = ‘Y_YYYYYYYYY_PRODUCT‘
AND DATECODE >= TO_DATE(‘2019-06-18 00:25:40‘,‘YYYY-MM-DD HH24:MI:SS‘)
AND DATECODE <= TO_DATE(‘2019-06-19 01:06:49‘,‘YYYY-MM-DD HH24:MI:SS‘)
AND BUSINESSTYPE = ‘NEW‘
) C,
Y_YYYYYYYY_PRODUCT@DBLINK A,
Z_ZZZZZZZZ_BENE@DBLINK B
WHERE A.ITEM_ID = C.FIELD1_CONTENT
AND A.XXXXXX_ID = B.XXXXXX_ID
AND A.XXXXXXX_1 = B.YYYYYYYY
AND B.XXXXXX_ID IS NOT NULL
AND B.REAL_NAME<>‘法定受益人‘
AND B.REAL_NAME<>‘法定继承人‘
AND B.REAL_NAME<>‘详见特别约定‘
AND B.REAL_NAME<>‘祥见特别约定‘
AND B.REAL_NAME<>‘法定‘
AND ( ( B.REAL_NAME IS NOT NULL
AND B.CERTI_TYPE IS NOT NULL
AND B.CERTI_CODE IS NOT NULL
)
OR
( B.REAL_NAME IS NOT NULL
AND B.TELEPHONE IS NOT NULL
)
);
修改前的执行计划，执行这个脚本大约4个小时没跑完。
SQL_ID acbfjjjak0vub, child number 0
-------------------------------------
INSERT INTO xxxxxxxxxx SELECT xxxxx FROM ( SELECT DISTINCT FIELD1_CONTENT
FROM xxxxxxxx_LOG@DBLINK WHERE UPPER(TABLENAME) =
‘Y_YYYYYYYY_PRODUCT‘ AND DATECODE >= TO_DATE(‘2019-06-18
00:25:40‘,‘YYYY-MM-DD HH24:MI:SS‘) AND DATECODE <=
TO_DATE(‘2019-06-19 01:06:49‘,‘YYYY-MM-DD HH24:MI:SS‘) AND
BUSINESSTYPE = ‘NEW‘ ) C, Y_YYYYYYYY_PRODUCT@DBLINK A,
Z_ZZZZZZZZ_BENE@DBLINK B WHERE A.ITEM_ID =
C.FIELD1_CONTENT AND A.XXXXXX_ID = B.XXXXXX_ID A
Plan hash value: 2832774925
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 108K(100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 377 | 108K (1)| 00:21:39 | | |
| 3 | MERGE JOIN CARTESIAN | | 606 | 194K| 107K (1)| 00:21:25 | | |
| 4 | REMOTE | Z_ZZZZZZZZ_BENE | 1 | 177 | 10849 (1)| 00:02:11 | DBLINK | R->S |
| 5 | BUFFER SORT | | 7277 | 1080K| 96163 (1)| 00:19:14 | | |
| 6 | VIEW | | 7277 | 1080K| 96163 (1)| 00:19:14 | | |
| 7 | SORT UNIQUE | | 7277 | 412K| 96163 (1)| 00:19:14 | | |
| 8 | REMOTE | xxxxxxxx_LOG | 7278 | 412K| 96162 (1)| 00:19:14 | DBLINK | R->S |
| 9 | REMOTE | Y_YYYYYYYY_PRODUCT | 1 | 48 | 2 (0)| 00:00:01 | DBLINK | R->S |
---------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT /*+ OPAQUE_TRANSFORM */ "BENE_ID","XXXXXX_ID","YYYYYYYY","BENE_TYPE","BENE_ORDER","REAL_N
AME","CERTI_TYPE","CERTI_CODE","TELEPHONE","BENE_RATE" FROM "Z_ZZZZZZZZ_BENE" "B" WHERE ("TELEPHONE"
IS NOT NULL OR "CERTI_TYPE" IS NOT NULL AND "CERTI_CODE" IS NOT NULL) AND "REAL_NAME"<>‘法定受益人‘ AND
"REAL_NAME"<>‘法定继承人‘ AND "REAL_NAME"<>‘详见特别约定‘ AND "REAL_NAME"<>‘祥见特别约定‘ AND "REAL_NAME"<>‘法定‘
(accessing ‘DBLINK‘ )
8 - SELECT /*+ OPAQUE_TRANSFORM */ "DATECODE","TABLENAME","FIELD1_CONTENT","BUSINESSTYPE" FROM
"xxxxxxxx_LOG" "xxxxxxxx_LOG" WHERE "DATECODE">=TO_DATE(‘ 2019-06-18 00:25:40‘, ‘syyyy-mm-dd
hh24:mi:ss‘) AND "BUSINESSTYPE"=‘NEW‘ AND UPPER("TABLENAME")=‘Y_YYYYYYYY_PRODUCT‘ AND
"DATECODE"<=TO_DATE(‘ 2019-06-19 01:06:49‘, ‘syyyy-mm-dd hh24:mi:ss‘) (accessing ‘DBLINK‘ )
9 - SELECT /*+ OPAQUE_TRANSFORM */ "ITEM_ID","XXXXXX_ID","XXXXXXX_1","UPDATE_TIME" FROM
"Y_YYYYYYYY_PRODUCT" "A" WHERE "ITEM_ID"=:1 AND "XXXXXX_ID"=:2 AND "XXXXXXX_1"=:3 (accessing ‘DBLINK‘ )
--省略处跟原SQL相同，只不过新增了/*+ cardinality(b 2000000) */
SELECT /*+ cardinality(b 2000000) */ xxxx FROM ( SELECT DISTINCT FIELD1_CONTENT FROM xxxxxxxx_LOG@DBLINK .... ;
修改之后的执行计划
Plan Hash Value : 1238862255
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7277 | 2081222 | 121654 | 00:24:20 |
| * 1 | HASH JOIN | | 7277 | 2081222 | 121654 | 00:24:20 |
| 2 | NESTED LOOPS | | 7277 | 1455400 | 110742 | 00:22:09 |
| 3 | VIEW | | 7277 | 1106104 | 96163 | 00:19:14 |
| 4 | HASH UNIQUE | | 7277 | 422066 | 96163 | 00:19:14 |
| 5 | REMOTE | xxxxxxxx_LOG | 7278 | 422124 | 96162 | 00:19:14 |
| 6 | REMOTE | Y_YYYYYYYY_PRODUCT | 1 | 48 | 2 | 00:00:01 |
| 7 | REMOTE | Z_ZZZZZZZZ_BENE | 2000000 | 172000000 | 10928 | 00:02:12 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."XXXXXX_ID"="B"."XXXXXX_ID" AND "A"."XXXXXXX_1"="B"."YYYYYYYY")
原文：https://www.cnblogs.com/fooobabar/p/11050775.html