Oracle从一条SQL优化中分析rownum和order by哪个先执行
Oracle从一条SQL优化中分析rownum和order by哪个先执行
最近遇到一条SQL（称为SQL 1）和对应的执行计划（PLAN 1）如下：
SELECT * Plan hash value: 2993585658 FROM (SELECT row_.*, rownum rownum_ FROM (SELECT tab0_.seq_number AS col_0_0_, --------------------------------------------------------------------------------------------------------------------------- tab0_.file_name AS col_1_0_, | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | tab0_.sender AS col_2_0_, --------------------------------------------------------------------------------------------------------------------------- tab0_.receive AS col_3_0_, | 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.58 | 122K| 3 | tab0_.re_time AS col_4_0_, |* 1 | VIEW | | 1 | 3 | 20 |00:00:00.58 | 122K| 3 | tab0_.trs_time AS col_5_0_, | 2 | COUNT | | 1 | | 129K|00:00:00.54 | 122K| 3 | tab0_.puti_position AS col_6_0_, | 3 | VIEW | | 1 | 3 | 129K|00:00:00.52 | 122K| 3 | tab0_.book_type AS col_7_0_ |* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| FT_INFORMATION | 1 | 3 | 129K|00:00:00.50 | 122K| 3 | FROM ft_information tab0_ |* 5 | INDEX RANGE SCAN DESCENDING | IDX_FT_RE_TIME | 1 | 13 | 465K|00:00:00.11 | 4611 | 0 | WHERE 1 = 1 --------------------------------------------------------------------------------------------------------------------------- AND tab0_.orde_type = '订单' AND (tab0_.re_time BETWEEN TO_DATE('2025-04-10', 'yyyy-mm-dd') Predicate Information (identified BY operation id): AND TO_DATE('2025-04-10 23:59:59','yyyy-mm-dd hh24:mi:ss')) --------------------------------------------------- ORDER BY tab0_.re_time DESC) row_) WHERE rownum_ <= 40 1 - filter(("ROWNUM_">20 AND "ROWNUM_"<=40)) AND rownum_ > 20; 4 - filter("TAB0_"."ORDE_TYPE"='订单') 5 - access("TAB0_"."RE_TIME"<=TO_DATE(' 2025-04-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "TAB0_"."RE_TIME">=TO_DATE(' 2025-04-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
稍微改写为如下SQL（称为SQL 2）和对应的执行计划（PLAN 2）：
SELECT * Plan hash value: 842684853 FROM (SELECT tab0_.seq_number AS col_0_0_, tab0_.file_name AS col_1_0_, ----------------------------------------------------------------------------------------------------------------- tab0_.sender AS col_2_0_, | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | tab0_.receive AS col_3_0_, ----------------------------------------------------------------------------------------------------------------- tab0_.re_time AS col_4_0_, | 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 71 | tab0_.trs_time AS col_5_0_, |* 1 | VIEW | | 1 | 3 | 20 |00:00:00.01 | 71 | tab0_.puti_position AS col_6_0_, |* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:00.01 | 71 | tab0_.book_type AS col_7_0_, |* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| FT_INFORMATION | 1 | 3 | 40 |00:00:00.01 | 71 | rownum AS rownum_ |* 4 | INDEX RANGE SCAN DESCENDING | IDX_FT_RE_TIME | 1 | 13 | 252 |00:00:00.01 | 8 | FROM ft_information tab0_ ----------------------------------------------------------------------------------------------------------------- WHERE 1 = 1 AND tab0_.orde_type = '订单' Predicate Information (identified BY operation id): AND tab0_.re_time >= TO_DATE('2025-04-10', 'yyyy-mm-dd') --------------------------------------------------- AND tab0_.re_time < TO_DATE('2025-04-11', 'YYYY-mm-dd') AND rownum <= 40 1 - filter("ROWNUM_">20) ORDER BY tab0_.re_time DESC) 2 - filter(ROWNUM<=40) WHERE rownum_ > 20; 3 - filter("TAB0_"."ORDE_TYPE"='订单') 4 - access("TAB0_"."RE_TIME"<TO_DATE(' 2025-04-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TAB0_"."RE_TIME">=TO_DATE(' 2025-04-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
首先这两条SQL的执行结果是一样没有问题的，改写后的执行速度和资源消耗确实更优了。
那么改写是等价的吗？
第一反应是看rownum和order by谁先执行？如果是order by先执行，如果rownum先执行那么是有问题的。
接下来我们分析分析。
从两者的执行计划看，访问索引IDX_FT_RE_TIME（对应字段为RE_TIME）均使用了“INDEX RANGE SCAN DESCENDING”（索引降序扫描）方式，这样获取到的数据直接就是降序的。
在按RE_TIME为2025-04-10和2025-04-11之间的数据降序后，取第21-40行的数据，因此在这两个执行计划的基础看，两个SQL是必然等价的。
另外一个证明是可以看到PLAN 2并没有“SORT”的排序相关的步骤，下边会有sort相关的执行计划，可以做个对比。
这里谈一谈为什么改动后的SQL 2更快消耗更少。
因为PLAN 2在id=4和id=3的步骤中，只需要按照降序的顺序取到满足前40条即可立刻停止对索引IDX_FT_RE_TIME的继续扫描，不单单是索引的读变少，更重要的是回表的次数也变少。
这里可以看到id=4的A-Rows在获取到252条数据后，即回表252次后，就找到了满足orde_type = '订单'的40条数据，这个时候id=4和id=3就结束并且不再继续执行。
我们对比PLAN 1的id=5可以看到，结合SQL 1的文本可以知道，此步骤是把所有RE_TIME为2025-04-10和2025-04-11之间的数据直接降序取到后，在回表（回表导致了更多的逻辑读）去过滤出所有满足orde_type = '订单'的数据，然后在取第21-40条的数据，这样自然更慢消耗更多。
大部分情况下rownum在where中就可以起到这么一个基本的作用：在获取到满足谓词条件和条数的数据量的情况下，停止继续获取数据。
既然我们知道通过“INDEX RANGE SCAN DESCENDING”下，SQL 1等价于SQL 2，那么如果执行计划没有“INDEX RANGE SCAN DESCENDING”还会等价吗？
最简单的是不通过索引进行全表扫描。
如下，从执行计划看，先id=5全表扫描获取满足re_time和orde_type的数据，然后id=3获取前40条，之后在id=2进行排序，这里虽然SQL没跑完但是分析看结果必然和SQL 1的结果不同，也就是full这个hint导致两个sql不在等价。
SELECT * --没跑完SQL下产生的执行计划，PLAN 3 FROM (SELECT /*+ full(tab0_) */ tab0_.seq_number AS col_0_0_, Plan hash value: 2512974390 tab0_.file_name AS col_1_0_, tab0_.sender AS col_2_0_, ----------------------------------------------------------------------------------------------------------------------- tab0_.receive AS col_3_0_, | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem | tab0_.re_time AS col_4_0_, ----------------------------------------------------------------------------------------------------------------------- tab0_.trs_time AS col_5_0_, | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | | | | tab0_.puti_position AS col_6_0_, |* 1 | VIEW | | 1 | 3 | 0 |00:00:00.01 | | | | tab0_.book_type AS col_7_0_, | 2 | SORT ORDER BY | | 1 | 3 | 0 |00:00:00.01 | 73728 | 73728 | | rownum AS rownum_ |* 3 | COUNT STOPKEY | | 1 | | 0 |00:00:00.01 | | | | FROM ft_information tab0_ | 4 | PARTITION RANGE SINGLE| | 1 | 3 | 0 |00:00:00.01 | | | | WHERE 1 = 1 |* 5 | TABLE ACCESS FULL | FT_INFORMATION | 1 | 3 | 0 |00:00:00.01 | | | | AND tab0_.orde_type = '订单' ----------------------------------------------------------------------------------------------------------------------- AND tab0_.re_time >= TO_DATE('2025-04-10', 'yyyy-mm-dd') AND tab0_.re_time < TO_DATE('2025-04-11', 'YYYY-mm-dd') Predicate Information (identified by operation id): AND rownum <= 40 --------------------------------------------------- ORDER BY tab0_.re_time DESC) WHERE rownum_ > 20; 1 - filter("ROWNUM_">20) 3 - filter(ROWNUM<=40) 5 - filter(("TAB0_"."RE_TIME">=TO_DATE(' 2025-04-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TAB0_"."ORDE_TYPE"='订单' AND "TAB0_"."RE_TIME"<TO_DATE(' 2025-04-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
再看一个，如果是普通的索引范围扫描也是有问题的。
如下，这里我用minus对比了下结果是不一样的，即index_asc这个hint也导致了sql不等价。
简单分析如下：
id=5通过索引范围扫描获取到RE_TIME为2025-04-10和2025-04-11之间的（已经升序的）数据，并通过id=3再次过滤满足“ORDE_TYPE='订单'”，一旦有40条之后，即可立刻停止对索引IDX_FT_RE_TIME的继续扫描，当然回表次数也少了。
但是这里是升序获得的40条数据已经是有问题了，因为re_time是从范围时间内的最小时间开始扫描的，而原本SQL 1是ORDER BY re_time DESC，取的是值最大的那21-40条。
并且这里id=2对有问题的40条数据重新降序，再输出结果。
SELECT * --PLAN 4 FROM (SELECT /*+ index_asc(t idx_ft_in_time) */ tab0_.seq_number AS col_0_0_, Plan hash value: 570792358 tab0_.file_name AS col_1_0_, tab0_.sender AS col_2_0_, --------------------------------------------------------------------------------------------------------------------------------------------- tab0_.receive AS col_3_0_, | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | tab0_.re_time AS col_4_0_, --------------------------------------------------------------------------------------------------------------------------------------------- tab0_.trs_time AS col_5_0_, | 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 51 | | | | tab0_.puti_position AS col_6_0_, |* 1 | VIEW | | 1 | 3 | 20 |00:00:00.01 | 51 | | | | tab0_.book_type AS col_7_0_, | 2 | SORT ORDER BY | | 1 | 3 | 40 |00:00:00.01 | 51 | 13312 | 13312 |12288 (0)| rownum AS rownum_ |* 3 | COUNT STOPKEY | | 1 | | 40 |00:00:00.01 | 51 | | | | FROM ft_information tab0_ |* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| FT_INFORMATION | 1 | 3 | 40 |00:00:00.01 | 51 | | | | WHERE 1 = 1 |* 5 | INDEX RANGE SCAN | IDX_FT_RE_TIME | 1 | 13 | 153 |00:00:00.01 | 4 | | | | AND tab0_.orde_type = '订单' --------------------------------------------------------------------------------------------------------------------------------------------- AND tab0_.re_time >= TO_DATE('2025-04-10', 'yyyy-mm-dd') AND tab0_.re_time < TO_DATE('2025-04-11', 'YYYY-mm-dd') Predicate Information (identified by operation id): AND rownum <= 40 --------------------------------------------------- ORDER BY tab0_.re_time DESC) WHERE rownum_ > 20; 1 - filter("ROWNUM_">20) 3 - filter(ROWNUM<=40) 4 - filter("TAB0_"."ORDE_TYPE"='订单') 5 - access("TAB0_"."RE_TIME">=TO_DATE(' 2025-04-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TAB0_"."RE_TIME"<TO_DATE(' 2025-04-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
回到标题，order by和rownum哪个先执行？
拿着这个问题取网上搜，结论很多都是有问题的。
实际上没有固定的结论，需要结合执行计划看，像PLAN 2中甚至都没有排序这个步骤，“INDEX RANGE SCAN DESCENDING”已经保证了有序，当然从最终结果看可以说是先order by再rownum。
而PLAN 3使用全表扫描，结合执行计划是先rownum再order by，这导致了改写的sql 2结果执行出问题了。
再看PLAN 4，虽然也是用了索引，但结合上面分析，可以看成先rownum再order by。
浙公网安备 33010602011771号