首页 > 其他 > 详细

通过hint干掉笛卡尔积

时间:2019-06-19 14:30:14      阅读:133      评论:0      收藏:0      [点我收藏+]
Z_ZZZZZZZZ_BENE 大约有400万数据
修改前
 
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
--省略处跟原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")

  

 

 

 


Z_ZZZZZZZZ_BENE 大约有400万数据


修改前
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‘ )
 
 

  
修改后
SELECT /*+ cardinality(b 2000000) */ 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
)
);
  
修改之后的执行计划

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

通过hint干掉笛卡尔积

原文:https://www.cnblogs.com/fooobabar/p/11050775.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!