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

        )

      );

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")