当前位置: 代码迷 >> Oracle技术 >> sql 优化,该如何解决
  详细解决方案

sql 优化,该如何解决

热度:486   发布时间:2016-04-24 08:41:07.0
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一把再关联
  相关解决方案