group by 与 over partition by 性能比较

Question

我有两个sql
sql一:
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)
AND    fl.upload_status = 'VK';
sql二：
SELECT *
FROM   conv_data_ul_file_line t1
WHERE  EXISTS (SELECT 1
        FROM   (SELECT t.data_ul_file_line_oid, t.cif_no, t.account_no, 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)
AND    t1.upload_status = 'VK';
这两个sql是找出anz_client_account 表中cif_no,account_no 重复的数据行
其中conv_data_ul_file_line表中大约有三百万条数据，anz_client_account中有两百万条数据，两个sql查询出来重复的记录总共144条但sql一查了五个小时，sql二却只查了几秒钟，在网上找了好多关于group by 与 over partition by 性能的比较，在涉及
排序的情况下，都说over partition by 性能要差很多，因为这两个sql没有排序，为什么over partition by会比group by
快这么多呢？有人知道原因吗？如果知道，请说得详细点行吗？谢谢了

Answer 1

首先，这两个SQL本身就隐含了排序的；
其次，在多次试验中，我认为over partition by比group by要快的，特别分组后还有过滤条件的，如having。
但如果一个是查了5个小时，一个几秒钟的话，个人感觉也是不正常的，也不至于相差这么多。LZ得去确认下，几秒钟的那次是不是没进行查询，而是直接从缓存中读取的结果。

Answer 2

oracle后台对group by、partition等都是有排序的，你可以这么看，DB先对数据进行排序，才方便对其进行分组，对吧？
再详细的信息你可以去百度下。

Answer 3

这2条SQL 效率差距应该不在排序上，第一个SQL查了2次anz_client_account，并且用EXISTS是对外层表做了NESTED LOOP，请分别贴出执行计划

Answer 4

SELECT t.data_ul_file_line_oid, t.cif_no, t.account_no, 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这条语句，返回多少条记录 sumcount 就要计算你多少次，数据量小的话根本就感觉不出来，数据量大了，你就知道有多慢了

Answer 5

你要测试两个SQL在数据库里的执行时间，应该在跑完第一条语句后，flush buffer_cache后在跑第二条SQL
你跑第一条把数据库都读到了buffer cache里了，在接着跑第二条，它会直接读buffer cache里的数据，不用在读磁盘了
有执行计划的话，你可以对比两条SQL的物理读和逻辑读，会非常的清楚