sql 优化

[lanita]

有下面两个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中数据最多

[wylsy1]

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一把再关联

[Rotel-刘志东]

优化是有先决条件的，是为了提高查询效率。还是减少磁盘的I/O操作。
具体要看一下执行计划了。

[我2我骄傲]

执行计划！

[lanita]

能不能说得具体点啊。。。

[lanita]

over ..PARTITION BY 这样的用法没用过。。要学习了

[luyun2011]

select * from conv_data_ul_file_line fl
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;