sql 优化
有下面两个sql:
//查询出anz_client_account 表中cif_no, account_no重复的记录
select * from conv_data_ul_file_line fl
WHERE EXISTS (SELECT 1
  FROM anz_client_account anz
  WHERE EXISTS (SELECT 1
  FROM anz_client_account aca
  WHERE anz.cif_no = aca.cif_no
  AND anz.account_no = aca.account_no
  GROUP BY aca.cif_no, aca.account_no
  HAVING COUNT(*) > 1)
  AND fl.data_ul_file_line_oid = anz.data_ul_file_line_oid);

//查询出 anz_client_account表中cif_no从第五位到最后 不存在于client_info_ts表中cust_nbr字段
select * from conv_data_ul_file_line fl
WHERE EXISTS (SELECT 1
  FROM anz_client_account anz
  WHERE NOT EXISTS (SELECT 1 FROM client_info_ts ts WHERE ts.cust_nbr = substr(anz.cif_no, 5))
  AND fl.data_ul_file_line_oid = anz.data_ul_file_line_oid);


这两个sql可进行优化？conv_data_ul_file_line，anz_client_account，client_info_ts中的数据非常多，以conv_data_ul_file_line中数据最多

------解决方案--------------------
优化是有先决条件的，是为了提高查询效率。还是减少磁盘的I/O操作。
具体要看一下执行计划了。
------解决方案--------------------
1. SELECT *
FROM conv_data_ul_file_line t1
WHERE EXISTS (SELECT 1
FROM (SELECT t.data_ul_file_line_oid, SUM(1) over(PARTITION BY cif_no, account_no) AS sumcount FROM anz_client_account t) t2
WHERE t1.data_ul_file_line_oid = t2.data_ul_file_line_oid
AND t2.sumcount > 1);

2. 这个从语句上没什么好优化的，看记录，如果anz_client_account，client_info_ts中使用到的那几个字段重复的很多，可以先distinct一把再关联