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

最新文章

  1. docker swarm-mode
  2. caffe下训练时遇到的一些问题汇总
  3. select下拉框插件（转）
  4. AspNet Mvc 路由解析中添加.html 等后缀 出现404错误的解决办法
  5. JMeter简介
  6. Java中sychronized方法与sychronized块区别
  7. SQLCONNECTION使用HTTP通信协议和中间件连接
  8. 那些年优秀的HTML5活动页面
  9. ZOJ1074 (最大和子矩阵 DP)
  10. Godaddy主机从购买到开通的详细图文教程（2013年）
  11. 从头开始建网站（三）DNS
  12. H5 canvas圆形的时钟
  13. poj 食物链
  14. 模板引擎之hogan.js
  15. 搭建基于hyperledger fabric的联盟社区（二） --环境配置
  16. restful知识点之二restframework视图
  17. Mac PyCharm专业版破解
  18. 通过event事件来控制红绿灯通行车辆
  19. Hadoop(21)-数据清洗(ELT)简单版
  20. css3创建多边形clip属性，可用来绘制不规则图形了

热门文章

  1. Contest - 第10届“新秀杯”ACM程序设计大赛现场热身赛 赛后信息（题解）
  2. 双外边距浮动bug；3像素文本偏移bug；IE6以下相对定位中的绝对定位bug
  3. 创建第一个freemarker
  4. 重写boost内存池
  5. Gson序列化对象时排除字段
  6. jquery之营销系统
  7. BitmapFactory 加载图片到内存
  8. playframework简单介绍
  9. Android -------- 从一个Fragment跳转到另一个Fragment
  10. 忘记了SqlServer的SA密码怎么办