SELECT *
FROM F_PTY_INDIV_TMP O
WHERE EXISTS
(SELECT 1
FROM F_PTY_INDIV F
WHERE O.PTY_ID = F.PTY_ID
AND O.CORP_ORG = F.CORP_ORG
AND O.SOURCE_CODE = F.SOURCE_CODE)
AND O.PTY_NAME || O.GENDER_CD || O.BIRTHDAY || O.CERT_TYPE ||
O.CERT_NO || O.SOCINSUR_NO || O.COUNTRY_CD || O.NATION ||
O.POLITICS_CD || O.MARRIAGE_STAT || O.FAMILY_ADDR || O.CUST_NAME ||
O.VOCATION_CD || O.TITLE_CD || O.TOPEDU_CD || O.TOP_DEGREE_CD ||
O.FINANCE_OR_NOT || O.OWNBANK_FLAG || O.INPUT_ORG || O.ADDRESS ||
O.DUTY_CD || O.LIVESTAT_CD || O.FARMERFLAG2 || O.CORP_BUS_CLASS ||
O.RELATIVEFLAG || O.COUNTRYCODE || O.REGION || O.CERT_PUT_DATE ||
O.CERT_END_DATE NOT IN
(SELECT PTY_NAME || GENDER_CD || BIRTHDAY || CERT_TYPE || CERT_NO ||
SOCINSUR_NO || COUNTRY_CD || NATION || POLITICS_CD ||
MARRIAGE_STAT || FAMILY_ADDR || CUST_NAME || VOCATION_CD ||
TITLE_CD || TOPEDU_CD || TOP_DEGREE_CD || FINANCE_OR_NOT ||
OWNBANK_FLAG || INPUT_ORG || ADDRESS || DUTY_CD ||
LIVESTAT_CD || FARMERFLAG2 || CORP_BUS_CLASS ||
RELATIVEFLAG || COUNTRYCODE || REGION || CERT_PUT_DATE ||
CERT_END_DATE
FROM F_PTY_INDIV
WHERE END_DT = TO_DATE('29991231', 'YYYY-MM-DD')
AND O.PTY_ID = PTY_ID
AND O.CORP_ORG = CORP_ORG
AND O.SOURCE_CODE = SOURCE_CODE)
Plan hash value: 3266576070
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 149K| 36M| | 115M (1)|385:19:18 |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 149K| 36M| 20M| 4683 (1)| 00:00:57 |
| 3 | INDEX FAST FULL SCAN | SYS_C0061953 | 541K| 13M| | 811 (1)| 00:00:10 |
| 4 | TABLE ACCESS FULL | F_PTY_INDIV_TMP | 149K| 32M| | 1176 (2)| 00:00:15 |
|* 5 | TABLE ACCESS BY INDEX ROWID| F_PTY_INDIV | 1 | 144 | | 775 (0)| 00:00:10 |
|* 6 | INDEX SKIP SCAN | SYS_C0061953 | 1 | | | 774 (0)| 00:00:10 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "F_PTY_INDIV" "F_PTY_INDIV" WHERE
"END_DT"=TO_DATE('29991231','YYYY-MM-DD') AND LNNVL(:B1||:B2||:B3||:B4||:B5||:B6||:B7||:B8||:B9|
|:B10||:B11||:B12||:B13||:B14||:B15||:B16||:B17||:B18||:B19||:B20||:B21||:B22||:B23||:B24||:B25|
|:B26||:B27||:B28||:B29<>"PTY_NAME"||"GENDER_CD"||INTERNAL_FUNCTION("BIRTHDAY")||"CERT_TYPE"||"C
ERT_NO"||"SOCINSUR_NO"||"COUNTRY_CD"||"NATION"||"POLITICS_CD"||"MARRIAGE_STAT"||"FAMILY_ADDR"||"
CUST_NAME"||"VOCATION_CD"||"TITLE_CD"||"TOPEDU_CD"||"TOP_DEGREE_CD"||"FINANCE_OR_NOT"||"OWNBANK_
FLAG"||"INPUT_ORG"||"ADDRESS"||"DUTY_CD"||"LIVESTAT_CD"||"FARMERFLAG2"||"CORP_BUS_CLASS"||"RELAT
IVEFLAG"||"COUNTRYCODE"||"REGION"||"CERT_PUT_DATE"||"CERT_END_DATE") AND "PTY_ID"=:B30 AND
"CORP_ORG"=:B31 AND "SOURCE_CODE"=:B32))
2 - access("O"."PTY_ID"="F"."PTY_ID" AND "O"."CORP_ORG"="F"."CORP_ORG" AND
"O"."SOURCE_CODE"="F"."SOURCE_CODE")
5 - filter("END_DT"=TO_DATE('29991231','YYYY-MM-DD') AND
LNNVL(:B1||:B2||:B3||:B4||:B5||:B6||:B7||:B8||:B9||:B10||:B11||:B12||:B13||:B14||:B15||:B16||:B1
7||:B18||:B19||:B20||:B21||:B22||:B23||:B24||:B25||:B26||:B27||:B28||:B29<>"PTY_NAME"||"GENDER_C
D"||INTERNAL_FUNCTION("BIRTHDAY")||"CERT_TYPE"||"CERT_NO"||"SOCINSUR_NO"||"COUNTRY_CD"||"NATION"
||"POLITICS_CD"||"MARRIAGE_STAT"||"FAMILY_ADDR"||"CUST_NAME"||"VOCATION_CD"||"TITLE_CD"||"TOPEDU
_CD"||"TOP_DEGREE_CD"||"FINANCE_OR_NOT"||"OWNBANK_FLAG"||"INPUT_ORG"||"ADDRESS"||"DUTY_CD"||"LIV
ESTAT_CD"||"FARMERFLAG2"||"CORP_BUS_CLASS"||"RELATIVEFLAG"||"COUNTRYCODE"||"REGION"||"CERT_PUT_D
ATE"||"CERT_END_DATE"))
6 - access("PTY_ID"=:B1 AND "SOURCE_CODE"=:B2 AND "CORP_ORG"=:B3)
filter("PTY_ID"=:B1 AND "CORP_ORG"=:B2 AND "SOURCE_CODE"=:B3)
可以看到在:
AND O.PTY_NAME || O.GENDER_CD || O.BIRTHDAY || O.CERT_TYPE ||
O.CERT_NO || O.SOCINSUR_NO || O.COUNTRY_CD || O.NATION ||
O.POLITICS_CD || O.MARRIAGE_STAT || O.FAMILY_ADDR || O.CUST_NAME ||
O.VOCATION_CD || O.TITLE_CD || O.TOPEDU_CD || O.TOP_DEGREE_CD ||
O.FINANCE_OR_NOT || O.OWNBANK_FLAG || O.INPUT_ORG || O.ADDRESS ||
O.DUTY_CD || O.LIVESTAT_CD || O.FARMERFLAG2 || O.CORP_BUS_CLASS ||
O.RELATIVEFLAG || O.COUNTRYCODE || O.REGION || O.CERT_PUT_DATE ||
O.CERT_END_DATE NOT IN
(SELECT PTY_NAME || GENDER_CD || BIRTHDAY || CERT_TYPE || CERT_NO ||
SOCINSUR_NO || COUNTRY_CD || NATION || POLITICS_CD ||
MARRIAGE_STAT || FAMILY_ADDR || CUST_NAME || VOCATION_CD ||
TITLE_CD || TOPEDU_CD || TOP_DEGREE_CD || FINANCE_OR_NOT ||
OWNBANK_FLAG || INPUT_ORG || ADDRESS || DUTY_CD ||
LIVESTAT_CD || FARMERFLAG2 || CORP_BUS_CLASS ||
RELATIVEFLAG || COUNTRYCODE || REGION || CERT_PUT_DATE ||
CERT_END_DATE
FROM F_PTY_INDIV
WHERE END_DT = TO_DATE('29991231', 'YYYY-MM-DD')
AND O.PTY_ID = PTY_ID
AND O.CORP_ORG = CORP_ORG
AND O.SOURCE_CODE = SOURCE_CODE)
这里没有展开,哪里没展开就改哪里。
select * FROM F_PTY_INDIV_TMP O
left join (SELECT PTY_ID,CORP_ORG,SOURCE_CODE, PTY_NAME || GENDER_CD || BIRTHDAY || CERT_TYPE || CERT_NO ||
SOCINSUR_NO || COUNTRY_CD || NATION || POLITICS_CD ||
MARRIAGE_STAT || FAMILY_ADDR || CUST_NAME || VOCATION_CD ||
TITLE_CD || TOPEDU_CD || TOP_DEGREE_CD || FINANCE_OR_NOT ||
OWNBANK_FLAG || INPUT_ORG || ADDRESS || DUTY_CD ||
LIVESTAT_CD || FARMERFLAG2 || CORP_BUS_CLASS ||
RELATIVEFLAG || COUNTRYCODE || REGION || CERT_PUT_DATE ||
CERT_END_DATE as s
FROM F_PTY_INDIV
WHERE END_DT = TO_DATE('29991231', 'YYYY-MM-DD') b
on (O.PTY_ID = b.PTY_ID
AND O.CORP_ORG = b.CORP_ORG
AND O.SOURCE_CODE = b.SOURCE_CODE and (O.PTY_NAME || O.GENDER_CD || O.BIRTHDAY || O.CERT_TYPE ||
O.CERT_NO || O.SOCINSUR_NO || O.COUNTRY_CD || O.NATION ||
O.POLITICS_CD || O.MARRIAGE_STAT || O.FAMILY_ADDR || O.CUST_NAME ||
O.VOCATION_CD || O.TITLE_CD || O.TOPEDU_CD || O.TOP_DEGREE_CD ||
O.FINANCE_OR_NOT || O.OWNBANK_FLAG || O.INPUT_ORG || O.ADDRESS ||
O.DUTY_CD || O.LIVESTAT_CD || O.FARMERFLAG2 || O.CORP_BUS_CLASS ||
O.RELATIVEFLAG || O.COUNTRYCODE || O.REGION || O.CERT_PUT_DATE ||
O.CERT_END_DATE) = b.s)
WHERE EXISTS
(SELECT 1
FROM F_PTY_INDIV F
WHERE O.PTY_ID = F.PTY_ID
AND O.CORP_ORG = F.CORP_ORG
AND O.SOURCE_CODE = F.SOURCE_CODE)
AND b.PTY_ID is null