记一次exists语句优化过程

我们经常会遇到带有exists这种形式的语句，官方描述如下，一般对应于子查询语句返回的布尔值：

 

在例子中可以看到：

SELECT department_id
  FROM departments d
  WHERE EXISTS
  (SELECT * FROM employees e
    WHERE d.department_id 
    = e.department_id)
   ORDER BY department_id;

 

 

 

 

 

 

 

 

原始sql如下：

UPDATE RICH_CUSTLOKPROFITNORMAL N
   SET N.FLAG = 1
 WHERE N.STATE = 1
   AND N.FLAG = 0
   AND EXISTS
 (SELECT 1
          FROM RICH_CUSTINTEREST         T,
               FINANCIAL_LOCKISSUEPROFIT F,
               FINANCIAL_ISSUE           I
         WHERE N.CUSTINTERESTID = T.ID
           AND T.ISSUEID = I.ISSUEID
           AND F.ISSUEID = I.ISSUEID
           AND N.ISSUEPROFITID = F.SERIALNO
           AND F.STATE = 1
           AND I.USEBENCHMARK = 0
           AND T.TYPE IN (0, 1)
           AND ((T.TYPE = 0 AND T.MATURITYDATE = :1 AND
               T.PAYDATE IS NOT NULL) OR (T.TYPE = 1 AND T.PAYDATE = :2))
           AND T.ISSUEID = :3)

由于sql语句为update语句，最终在生产环境改为如下方式：

select count(*) from  RICH_CUSTLOKPROFITNORMAL N
WHERE N.FLAG = 0
     and exists (select 1 
                 from FINANCIAL_LOCKISSUEPROFIT f
                 where f.ISSUEID=n.ISSUEID
                 and N.ISSUEPROFITID = F.SERIALNO
                 and f.state=1)

and exists
(select 1
          FROM RICH_CUSTINTEREST         T,
               FINANCIAL_ISSUE           I
         WHERE N.CUSTINTERESTID = T.ID
           AND T.ISSUEID = I.ISSUEID
           AND I.USEBENCHMARK = 0
           AND T.TYPE IN (0, 1)
           AND ((T.TYPE = 0 AND T.MATURITYDATE = to_date('20180813','yyyymmdd') AND
               T.PAYDATE IS NOT NULL) OR (T.TYPE = 1 AND T.PAYDATE = to_date('20180813','yyyymmdd')))
           AND T.ISSUEID = 'AMHQRH63DGC')
and n.state=1;