Z_ZZZZZZZZ_BENE 大约有400万数据 修改前 SELECT xxxx FROM ( SELECT DISTINCT FIELD1_CONTENT FROM [emailprotected] WHERE UPPER(TABLENAME) = ‘Y_YYYYYYYYY_PRODUCT‘ AND DATECODE = TO_DATE(‘2019-06-18 00:25:40‘,‘YYYY-MM-DD HH24:M
SELECT xxxx
FROM (
SELECT DISTINCT FIELD1_CONTENT
FROM [email protected]
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,
[email protected] A,
[email protected] 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 [email protected]LINK 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, [email protected] A,
[email protected] 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 [email protected] .... ;
修改之后的执行计划
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 [email protected] 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, [email protected] A, [email protected] 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 [email protected] 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, [email protected] A, [email protected] 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 [email protected] 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, [email protected] A, [email protected] 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")