Oracle insert 导致hint DRIVING_SITE失效解决方案

业务是将远程的表的数据同步到本地。
数据库是Oracle 11.2.0.4.0
INSERT INTO GG_ISSUE
      (ISSUE_ID,
       CONTRACT_ID,
       ISSUE_DATE,
       PROJECT_ID)
      SELECT /*+DRIVING_SITE(ISSUE)*/  /*+DRIVING_SITE(PRO)*/ '09SYN'||ISSUE.ISSUE_ID,
             ISSUE.CONTRACT_ID,
             ISSUE.ISSUE_DATE,
             ISSUE.PROJECT_ID
        FROM GG_ISSUE@dblinkname
               ISSUE, GG_PROJECT@dblinkname
              PRO WHERE ISSUE.PROJECT_ID = PRO.PROJECT_ID
         AND ISSUE.ISSUE_TYPE <> 3
         AND ISSUE.ISSUE_TYPE > 0
         AND ISSUE.ISSUE_ID NOT IN (SELECT  SUBSTR(MI.ISSUE_ID,6,LENGTH(ISSUE_ID))  FROM   GG_ISSUE MI WHERE  SUBSTR(MI.DATA_AREA, 0, 2) =  '09')
         AND ISSUE.ISSUE_STATUS = 'audited';
原SQL执行非常缓慢,20分钟都没有反映，发现执行计划相当糟糕：
----------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  | Bytes    | Cost   | Time     |
----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |             | 67925 | 31381350 | 457737 | 01:31:33 |
|   1 |   LOAD TABLE CONVENTIONAL | GG_ISSUE    |       |          |        |          |
| * 2 |    FILTER                 |             |       |          |        |          |
|   3 |     NESTED LOOPS          |             | 67926 | 31381812 |   2973 | 00:00:36 |
|   4 |      REMOTE               | GG_ISSUE    | 67926 | 29547810 |   2855 | 00:00:35 |
|   5 |      REMOTE               | GG_PROJECT |     1 |       27 |      0 | 00:00:01 |
| * 6 |     TABLE ACCESS FULL     | GG_ISSUE    |     2 |       58 |      7 | 00:00:01 |
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter( NOT EXISTS (SELECT 0 FROM "GG_ISSUE" "MI" WHERE SUBSTR("MI"."DATA_AREA",0,2)='09' AND LNNVL(SUBSTR("MI"."ISSUE_ID",6,LENGTH("ISSUE_ID"))<>:B1)))
* 6 - filter(SUBSTR("MI"."DATA_AREA",0,2)='09' AND LNNVL(SUBSTR("MI"."ISSUE_ID",6,LENGTH("ISSUE_ID"))<>:B1))
 
去掉insert后直接执行select比较快,查询所有数据2千多条需要15s，执行计划如下：
---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows   | Bytes     | Cost  | Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE   |                 | 247088 | 120578944 | 29555 | 00:05:55 |
| * 1 |   HASH JOIN RIGHT ANTI NA |                 | 247088 | 120578944 | 29555 | 00:05:55 |
|   2 |    REMOTE                 | GG_ISSUE        |  14672 |    572208 | 25088 | 00:05:02 |
|   3 |    NESTED LOOPS           |                 | 247089 | 110942961 |  4466 | 00:00:54 |
|   4 |     PARTITION RANGE ALL   |                 | 247938 | 107853030 |  4452 | 00:00:54 |
| * 5 |      TABLE ACCESS FULL    | GG_ISSUE        | 247938 | 107853030 |  4452 | 00:00:54 |
| * 6 |     INDEX UNIQUE SCAN     | PK_PROJECT_ID_T |      1 |        14 |     0 | 00:00:01 |
---------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A2"."ISSUE_ID"=SUBSTR("A3"."ISSUE_ID",6,LENGTH("A3"."ISSUE_ID")))
* 5 - filter("A2"."ISSUE_TYPE"<>3 AND "A2"."ISSUE_STATUS"='audited' AND "A2"."ISSUE_TYPE">0)
* 6 - access("A2"."PROJECT_ID"="A1"."PROJECT_ID")


说明insert 导致/*+DRIVING_SITE(ISSUE)*/失效。如果让SQL走上正确的执行计划呢？有几种方案尝试一下：
1.加hint告诉CBO表上有多少数据，观察执行计划是否有变。
2.用merge into，观察执行计划是否有变。
3.将远程的关联表作为视图，然后通过dblink访问视图。

验证方案1：
 SELECT /*+use_hash(PRO,ISSUE) CARDINALITY(PRO 1000000) CARDINALITY(ISSUE 2000000)*/  '09SYN'||ISSUE.ISSUE_ID,     
--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows       | Bytes         | Cost        | Time      |
--------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |             | 3596461335 | 1661565136770 | 24935447682 | 999:59:59 |
|   1 |   LOAD TABLE CONVENTIONAL | GG_ISSUE    |            |               |             |           |
| * 2 |    FILTER                 |             |            |               |             |           |
| * 3 |     HASH JOIN             |             | 3596475454 | 1661571659748 |      462539 |  01:32:31 |
|   4 |      REMOTE               | GG_PROJECT |   10000000 |     270000000 |          64 |  00:00:01 |
|   5 |      REMOTE               | GG_ISSUE    |   20000000 |    8700000000 |        3578 |  00:00:43 |
| * 6 |     TABLE ACCESS FULL     | GG_ISSUE    |          2 |            58 |           7 |  00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter( NOT EXISTS (SELECT 0 FROM "GG_ISSUE" "MI" WHERE SUBSTR("MI"."DATA_AREA",0,2)='09' AND LNNVL(SUBSTR("MI"."ISSUE_ID",6,LENGTH("ISSUE_ID"))<>:B1)))
* 3 - access("ISSUE"."PROJECT_ID"="PRO"."PROJECT_ID")
* 6 - filter(SUBSTR("MI"."DATA_AREA",0,2)='09' AND LNNVL(SUBSTR("MI"."ISSUE_ID",6,LENGTH("ISSUE_ID"))<>:B1))

 SELECT /*+use_hash(PRO,ISSUE) CARDINALITY(PRO 1000000) CARDINALITY(ISSUE 2000000)*/  '09SYN'||ISSUE.ISSUE_ID,     
 SELECT  /*+use_hash(PRO,ISSUE) CARDINALITY(MI 2000000)*/  SUBSTR(MI.ISSUE_ID,6,LENGTH(ISSUE_ID))  FROM   GG_ISSUE MI WHERE  SUBSTR(MI.DATA_AREA, 0, 2) =  '09'
-------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows     | Bytes       | Cost     | Time      |
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |             | 35964613 | 16615651206 | 71116178 | 237:03:15 |
|   1 |   LOAD TABLE CONVENTIONAL | GG_ISSUE    |          |             |          |           |
| * 2 |    FILTER                 |             |          |             |          |           |
| * 3 |     HASH JOIN             |             | 35964755 | 16615716810 |    47294 |  00:09:28 |
|   4 |      REMOTE               | GG_PROJECT |  1000000 |    27000000 |       64 |  00:00:01 |
|   5 |      REMOTE               | GG_ISSUE    |  2000000 |   870000000 |     2925 |  00:00:36 |
| * 6 |     TABLE ACCESS FULL     | GG_ISSUE    |  2000000 |    58000000 |        2 |  00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter( NOT EXISTS (SELECT /*+ OPT_ESTIMATE (TABLE "MI" ROWS=2000000.000000 ) */ 0 FROM "GG_ISSUE" "MI" WHERE SUBSTR("MI"."DATA_AREA",0,2)='09' AND
  LNNVL(SUBSTR("MI"."ISSUE_ID",6,LENGTH("ISSUE_ID"))<>:B1)))
* 3 - access("ISSUE"."PROJECT_ID"="PRO"."PROJECT_ID")
* 6 - filter(SUBSTR("MI"."DATA_AREA",0,2)='09' AND LNNVL(SUBSTR("MI"."ISSUE_ID",6,LENGTH("ISSUE_ID"))<>:B1))
FILTER的算法类似nestloop,这样的执行计划很难执行出结果来

验证方案2：
merge into GG_ISSUE MI
using( SELECT /*+DRIVING_SITE(ISSUE)*/  /*+DRIVING_SITE(PRO)*/ 
             ISSUE.ISSUE_ID,
             ISSUE.CONTRACT_ID,
             ISSUE.ISSUE_DATE
        FROM GG_ISSUE@dblinkname  ISSUE, 
         GG_PROJECT@dblinkname PRO 
         WHERE ISSUE.PROJECT_ID = PRO.PROJECT_ID
         AND ISSUE.ISSUE_TYPE <> 3
         AND ISSUE.ISSUE_TYPE > 0
         AND ISSUE.ISSUE_STATUS = 'audited') bb
      on(SUBSTR(MI.ISSUE_ID,6,LENGTH(MI.ISSUE_ID))=bb.ISSUE_ID)
      when not matched then
      insert(ISSUE_ID,CONTRACT_ID,ISSUE_DATE) values(bb.ISSUE_ID,bb.CONTRACT_ID,bb.ISSUE_DATE)
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows   | Bytes     | Cost   | Time     |
-----------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT,GOAL=ALL_ROWS           |        | 188585335 | 73193  | 00:01:28 | 
|   1 |   MERGE                        | GG_ISSUE |        |           |        |          |
| * 2 |    VIEW                 |          |        |           |        |          |
| * 3 |      HASH JOIN OUTER           |          | 347938 | 188585335 |  73193 | 00:01:28 |
|   4 |       VIEW                     |   |        |   3328374 |   2971 | 00:00:32 |
|   5 |         REMOTE                 |          | 1      |           |        | 00:00:00 |
| * 6 |     TABLE ACCESS FULL          | GG_ISSUE | 247938 | 635299745 |  39254 | 00:00:54 |
-----------------------------------------------------------------------------------------------
执行计划变得正常，实测12s能够完成。由于方案2解决了问题，方案3就不用验证了。