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