Oracle关于WINDOW SORT PUSHED RANK一些探究猜想
Oracle关于WINDOW SORT PUSHED RANK一些探究猜想
最近遇到一条SQL，开发让我做出优化
with tmp as (select * from T_VERIFY_APPLY ty where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate), tmp1 as (select count(*) as month_call_num from tmp where to_char(sysdate, 'yyyy/mm') = to_char(tmp.create_time, 'yyyy/mm')), tmp2 as (select count(*) as last_year_month from tmp where to_char(ADD_MONTHS(sysdate, -12), 'yyyy/mm') = to_char(tmp.create_time, 'yyyy/mm')), tmp3 as (select count(*) as month_on_month from tmp where to_char(ADD_MONTHS(sysdate, -1), 'yyyy/mm') = to_char(tmp.create_time, 'yyyy/mm')), tmp4 as (select count(*) as this_year from tmp where to_char(sysdate, 'yyyy') = to_char(tmp.create_time, 'yyyy')), tmp5 as (select count(*) as past_year_num from tmp where to_char(ADD_MONTHS(sysdate, -12), 'yyyy') = to_char(tmp.create_time, 'yyyy')), temp as (select tl.entry_id, ty.create_time, ty.sender_id, row_number() over(partition by tl.entry_id order by 1) rn from t_verify_apply_list tl --418M left join T_VERIFY_APPLY ty --14M on ty.head_seq_no = tl.head_seq_no where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate), tmp6 as (select count(temp.entry_id) month_goods_num from temp where temp.rn = 1 and to_char(sysdate, 'yyyy/mm') = to_char(temp.create_time, 'yyyy/mm')), tmp7 as (select count(temp.entry_id) month_goods_with from temp where temp.rn = 1 and to_char(ADD_MONTHS(sysdate, -12), 'yyyy/mm') = to_char(temp.create_time, 'yyyy/mm')), tmp8 as (select count(temp.entry_id) this_month_goods_with from temp where temp.rn = 1 and to_char(ADD_MONTHS(sysdate, -1), 'yyyy') = to_char(temp.create_time, 'yyyy')), tmp9 as (select count(temp.entry_id) this_year_goods_with from temp where temp.rn = 1 and to_char(sysdate, 'yyyy') = to_char(temp.create_time, 'yyyy')), tmp10 as (select count(temp.entry_id) year_goods_than from temp where temp.rn = 1 and to_char(ADD_MONTHS(sysdate, -12), 'yyyy') = to_char(temp.create_time, 'yyyy')) SELECT tmp1.month_call_num, tmp2.last_year_month, tmp3.month_on_month, tmp4.this_year, tmp5.past_year_num, tmp6.month_goods_num, tmp7.month_goods_with, tmp8.this_month_goods_with, tmp9.this_year_goods_with, tmp10.year_goods_than from tmp1, tmp2, tmp3, tmp4, tmp5, tmp6, tmp7, tmp8, tmp9, tmp10;
别看SQL很长，实际上“架构”很简单。我给优化成如下SQL了。
with tmp as (select count(case when to_char(ty.create_time, 'yyyy/mm') = to_char(sysdate, 'yyyy/mm') then 1 else null end) month_call_num, count(case when to_char(ty.create_time, 'yyyy/mm') = to_char(ADD_MONTHS(sysdate, -12), 'yyyy/mm') then 1 else null end) last_year_month, count(case when to_char(ty.create_time, 'yyyy/mm') = to_char(ADD_MONTHS(sysdate, -1), 'yyyy/mm') then 1 else null end) month_on_month, count(case when to_char(ty.create_time, 'yyyy') = to_char(sysdate, 'yyyy') then 1 else null end) this_year, count(case when to_char(ty.create_time, 'yyyy') = to_char(ADD_MONTHS(sysdate, -12), 'yyyy') then 1 else null end) past_year_num from T_VERIFY_APPLY ty where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate), temp as (select count(case when to_char(create_time, 'yyyy/mm') = to_char(sysdate, 'yyyy/mm') then 1 else null end) month_goods_num, count(case when to_char(create_time, 'yyyy/mm') = to_char(ADD_MONTHS(sysdate, -12), 'yyyy/mm') then 1 else null end) month_goods_with, count(case when to_char(create_time, 'yyyy') = to_char(ADD_MONTHS(sysdate, -1), 'yyyy') then 1 else null end) this_month_goods_with, count(case when to_char(create_time, 'yyyy') = to_char(sysdate, 'yyyy') then 1 else null end) this_year_goods_with, count(case when to_char(create_time, 'yyyy') = to_char(ADD_MONTHS(sysdate, -12), 'yyyy') then 1 else null end) year_goods_than from ( select /*+ no_merge */ create_time from ( select ty.create_time, row_number() over(partition by tl.entry_id order by 1) rn from t_verify_apply_list tl,T_VERIFY_APPLY ty where ty.head_seq_no = tl.head_seq_no and ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate and tl.entry_id is not null ) where rn = 1) ) SELECT tmp.month_call_num, tmp.last_year_month, tmp.month_on_month, tmp.this_year, tmp.past_year_num, temp.month_goods_num, temp.month_goods_with, temp.this_month_goods_with, temp.this_year_goods_with, temp.year_goods_than from tmp, temp;
两者等价，但是会引起wrong result的bug出来，这个后边在单独写一篇另说。
扯远了。
拿出改造后SQL中的部分，暂且称之为SQL1。
select * from (select tl.entry_id, ty.create_time, ty.sender_id, row_number() over(partition by tl.entry_id order by ty.create_time) rn from t_verify_apply_list tl left join T_VERIFY_APPLY ty on ty.head_seq_no = tl.head_seq_no where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate) where rn = 1;
将SQL1中rn=1条件改为rn=0，称为SQL0，
改为rn=10000，称为SQL10000。
就不一一展示了。
再使用with as等价改为为SQL_wa如下：（必须使用hint：materialize不然无法将子查询固化为一张“临时表”）
with tmp as (select /*+ materialize */ tl.entry_id, ty.create_time, ty.sender_id, row_number() over(partition by tl.entry_id order by ty.create_time) rn from t_verify_apply_list tl left join T_VERIFY_APPLY ty on ty.head_seq_no = tl.head_seq_no where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate) select * from tmp where tmp.rn = 1;
其中，SQL1，SQL0,SQL10000只是改变了条件rn的等值判断，实际上暂时称为同一类的语句。
而SQL_wa只是用with as改造而已，虽然等价但是分为另一类语句。
探究1：谓词推入？
执行SQL1之后，看看执行计划：
Plan hash value: 1267599599 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 31360 |00:00:05.53 | 56828 | 1044 | 1044 | | | | | |* 1 | VIEW | | 1 | 3093K| 31360 |00:00:05.53 | 56828 | 1044 | 1044 | | | | | |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 65060 |00:00:05.51 | 56828 | 1044 | 1044 | 9396K| 1191K| 97M (1)| 9216 | |* 3 | FILTER | | 1 | | 3133K|00:00:02.32 | 56793 | 0 | 0 | | | | | |* 4 | HASH JOIN | | 1 | 3093K| 3133K|00:00:01.92 | 56793 | 0 | 0 | 4112K| 1291K| 5579K (0)| | |* 5 | TABLE ACCESS FULL | T_VERIFY_APPLY | 1 | 34791 | 35430 |00:00:00.03 | 1765 | 0 | 0 | | | | | | 6 | TABLE ACCESS FULL | T_VERIFY_APPLY_LIST | 1 | 3093K| 3133K|00:00:00.64 | 55028 | 0 | 0 | | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"=1) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TL"."ENTRY_ID" ORDER BY "TY"."CREATE_TIME")<=1) 3 - filter(SYSDATE@!>=ADD_MONTHS(SYSDATE@!,-12)) 4 - access("TY"."HEAD_SEQ_NO"="TL"."HEAD_SEQ_NO") 5 - filter(("TY"."SENDER_ID"='SWBHAP' AND INTERNAL_FUNCTION("TY"."RESULT_ID") AND "TY"."CREATE_TIME">=ADD_MONTHS(SYSDATE@!,-12) AND "TY"."CREATE_TIME"<=SYSDATE@!))
执行SQL_wa之后，看看执行计划：
Plan hash value: 129230774 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 31364 |00:00:11.56 | 95974 | 66248 | 66239 | | | | | | 1 | TEMP TABLE TRANSFORMATION | | 1 | | 31364 |00:00:11.56 | 95974 | 66248 | 66239 | | | | | | 2 | LOAD AS SELECT | | 1 | | 0 |00:00:10.26 | 75481 | 47860 | 66239 | 530K| 530K| 530K (0)| | | 3 | WINDOW SORT | | 1 | 3093K| 3133K|00:00:08.77 | 56804 | 47860 | 47851 | 420M| 6773K| 97M (1)| 374K| |* 4 | FILTER | | 1 | | 3133K|00:00:02.31 | 56793 | 9 | 0 | | | | | |* 5 | HASH JOIN | | 1 | 3093K| 3133K|00:00:01.95 | 56793 | 9 | 0 | 4112K| 1291K| 4635K (0)| | |* 6 | TABLE ACCESS FULL | T_VERIFY_APPLY | 1 | 34791 | 35436 |00:00:00.04 | 1765 | 0 | 0 | | | | | | 7 | TABLE ACCESS FULL | T_VERIFY_APPLY_LIST | 1 | 3093K| 3133K|00:00:00.70 | 55028 | 9 | 0 | | | | | |* 8 | VIEW | | 1 | 3093K| 31364 |00:00:01.29 | 20487 | 18388 | 0 | | | | | | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6B51_7DA5A5E2 | 1 | 3093K| 3133K|00:00:00.91 | 20487 | 18388 | 0 | | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(SYSDATE@!>=ADD_MONTHS(SYSDATE@!,-12)) 5 - access("TY"."HEAD_SEQ_NO"="TL"."HEAD_SEQ_NO") 6 - filter(("TY"."SENDER_ID"='SWBHAP' AND INTERNAL_FUNCTION("TY"."RESULT_ID") AND "TY"."CREATE_TIME">=ADD_MONTHS(SYSDATE@!,-12) AND "TY"."CREATE_TIME"<=SYSDATE@!)) 8 - filter("TMP"."RN"=1)
两者执行计划不同，SQL_wa执行时间明显更久一点。
其中，SQL_wa的执行计划id=3：WINDOW SORT为分析函数（也叫开窗函数）特有的，SQL1的执行计划id=2：WINDOW SORT PUSHED RANK同样也是。
两者区别在于SQL1的有个PUSHED RANK，表示将row_number() over(partition by tl.entry_id order by ty.create_time)<=1推入视图中（注意是<=，不是=，这点后边在探究）。
参考链接https://www.modb.pro/db/28980处所说的：
看起来是因为谓词推入之后SQL1才会比SQL_wa执行的更快。
但是实际上可能只是类似于谓词推入的技术而已，因为对SQL1使用no_pushed_pred并不会生效使得出现WINDOW SORT，还是会推入。
select * from (select /*+ no_push_pred no_merge */ tl.entry_id, ty.create_time, ty.sender_id, row_number() over(partition by tl.entry_id order by ty.create_time) rn from t_verify_apply_list tl left join T_VERIFY_APPLY ty on ty.head_seq_no = tl.head_seq_no where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate) where rn = 1; Plan hash value: 1267599599 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 31391 |00:00:05.56 | 56828 | 1045 | 1045 | | | | | |* 1 | VIEW | | 1 | 3093K| 31391 |00:00:05.56 | 56828 | 1045 | 1045 | | | | | |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 65123 |00:00:05.54 | 56828 | 1045 | 1045 | 9405K| 1191K| 97M (1)| 9216 | |* 3 | FILTER | | 1 | | 3135K|00:00:02.40 | 56793 | 0 | 0 | | | | | |* 4 | HASH JOIN | | 1 | 3093K| 3135K|00:00:01.99 | 56793 | 0 | 0 | 4112K| 1291K| 4601K (0)| | |* 5 | TABLE ACCESS FULL | T_VERIFY_APPLY | 1 | 34791 | 35468 |00:00:00.04 | 1765 | 0 | 0 | | | | | | 6 | TABLE ACCESS FULL | T_VERIFY_APPLY_LIST | 1 | 3093K| 3135K|00:00:00.74 | 55028 | 0 | 0 | | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"=1) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TL"."ENTRY_ID" ORDER BY "TY"."CREATE_TIME")<=1) 3 - filter(SYSDATE@!>=ADD_MONTHS(SYSDATE@!,-12)) 4 - access("TY"."HEAD_SEQ_NO"="TL"."HEAD_SEQ_NO") 5 - filter(("TY"."SENDER_ID"='SWBHAP' AND INTERNAL_FUNCTION("TY"."RESULT_ID") AND "TY"."CREATE_TIME">=ADD_MONTHS(SYSDATE@!,-12) AND "TY"."CREATE_TIME"<=SYSDATE@!))
不知道除了with as之外怎么阻止推入。，
探究2：分析函数(开窗函数)推入值对消耗的影响
从SQL1,SQL0,SQL10000的执行计划分别放在下边做对比。
SQL1执行计划：
Plan hash value: 1267599599 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 31435 |00:00:05.53 | 56828 | 1046 | 1046 | | | | | |* 1 | VIEW | | 1 | 3093K| 31435 |00:00:05.53 | 56828 | 1046 | 1046 | | | | | |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 65213 |00:00:05.52 | 56828 | 1046 | 1046 | 9414K| 1192K| 97M (1)| 9216 | |* 3 | FILTER | | 1 | | 3138K|00:00:02.32 | 56793 | 0 | 0 | | | | | |* 4 | HASH JOIN | | 1 | 3093K| 3138K|00:00:01.92 | 56793 | 0 | 0 | 4112K| 1291K| 5579K (0)| | |* 5 | TABLE ACCESS FULL | T_VERIFY_APPLY | 1 | 34791 | 35519 |00:00:00.04 | 1765 | 0 | 0 | | | | | | 6 | TABLE ACCESS FULL | T_VERIFY_APPLY_LIST | 1 | 3093K| 3138K|00:00:00.64 | 55028 | 0 | 0 | | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"=1) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TL"."ENTRY_ID" ORDER BY "TY"."CREATE_TIME")<=1) 3 - filter(SYSDATE@!>=ADD_MONTHS(SYSDATE@!,-12)) 4 - access("TY"."HEAD_SEQ_NO"="TL"."HEAD_SEQ_NO") 5 - filter(("TY"."SENDER_ID"='SWBHAP' AND INTERNAL_FUNCTION("TY"."RESULT_ID") AND "TY"."CREATE_TIME">=ADD_MONTHS(SYSDATE@!,-12) AND "TY"."CREATE_TIME"<=SYSDATE@!))
SQL0执行计划：
Plan hash value: 1267599599 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:10.30 | 56828 | 48295 | 48295 | | | | | |* 1 | VIEW | | 1 | 3093K| 0 |00:00:10.30 | 56828 | 48295 | 48295 | | | | | |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 3138K|00:00:10.00 | 56828 | 48295 | 48295 | 424M| 6802K| 97M (1)| 378K| |* 3 | FILTER | | 1 | | 3138K|00:00:02.66 | 56793 | 0 | 0 | | | | | |* 4 | HASH JOIN | | 1 | 3093K| 3138K|00:00:02.22 | 56793 | 0 | 0 | 4112K| 1291K| 5583K (0)| | |* 5 | TABLE ACCESS FULL | T_VERIFY_APPLY | 1 | 34791 | 35523 |00:00:00.03 | 1765 | 0 | 0 | | | | | | 6 | TABLE ACCESS FULL | T_VERIFY_APPLY_LIST | 1 | 3093K| 3138K|00:00:00.88 | 55028 | 0 | 0 | | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"=0) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TL"."ENTRY_ID" ORDER BY "TY"."CREATE_TIME")<=0) 3 - filter(SYSDATE@!>=ADD_MONTHS(SYSDATE@!,-12)) 4 - access("TY"."HEAD_SEQ_NO"="TL"."HEAD_SEQ_NO") 5 - filter(("TY"."SENDER_ID"='SWBHAP' AND INTERNAL_FUNCTION("TY"."RESULT_ID") AND "TY"."CREATE_TIME">=ADD_MONTHS(SYSDATE@!,-12) AND "TY"."CREATE_TIME"<=SYSDATE@!))
SQL10000执行计划：
Plan hash value: 1267599599 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:10.73 | 56828 | 48295 | 48295 | | | | | |* 1 | VIEW | | 1 | 3093K| 0 |00:00:10.73 | 56828 | 48295 | 48295 | | | | | |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 3138K|00:00:10.42 | 56828 | 48295 | 48295 | 424M| 6802K| 97M (1)| 378K| |* 3 | FILTER | | 1 | | 3138K|00:00:02.54 | 56793 | 0 | 0 | | | | | |* 4 | HASH JOIN | | 1 | 3093K| 3138K|00:00:02.10 | 56793 | 0 | 0 | 4112K| 1291K| 4600K (0)| | |* 5 | TABLE ACCESS FULL | T_VERIFY_APPLY | 1 | 34791 | 35523 |00:00:00.04 | 1765 | 0 | 0 | | | | | | 6 | TABLE ACCESS FULL | T_VERIFY_APPLY_LIST | 1 | 3093K| 3138K|00:00:00.78 | 55028 | 0 | 0 | | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"=10000) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TL"."ENTRY_ID" ORDER BY "TY"."CREATE_TIME")<=10000) 3 - filter(SYSDATE@!>=ADD_MONTHS(SYSDATE@!,-12)) 4 - access("TY"."HEAD_SEQ_NO"="TL"."HEAD_SEQ_NO") 5 - filter(("TY"."SENDER_ID"='SWBHAP' AND INTERNAL_FUNCTION("TY"."RESULT_ID") AND "TY"."CREATE_TIME">=ADD_MONTHS(SYSDATE@!,-12) AND "TY"."CREATE_TIME"<=SYSDATE@!))
这3个执行计划都看id=2步骤的消耗。
从结果看，SQL0和SQL10000是一样的消耗的，只有SQL1消耗较少。
一开始我还以为SQL0会秒出，因为不可能存在rn=0的结果，没想到还是没那么智能。
结合谓词信息：2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TL"."ENTRY_ID" ORDER BY "TY"."CREATE_TIME")<=？？？)
----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------------------------------------------------------------- rn=1 |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 65213 |00:00:05.52 | 56828 | 1046 | 1046 | 9414K| 1192K| 97M (1)| 9216 | rn=0 |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 3138K|00:00:10.00 | 56828 | 48295 | 48295 | 424M| 6802K| 97M (1)| 378K| rn=10000 |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 3138K|00:00:10.42 | 56828 | 48295 | 48295 | 424M| 6802K| 97M (1)| 378K| -----------------------------------------------------------------------------------------------------------------------------------------------------------------
此处的Buffers均为56828，是由于全表扫描T_VERIFY_APPLY和T_VERIFY_APPLY_LIST导致的。
其他的Reads和Writes则当rn=1时远小于其他两个，另外两个是一样的消耗。这才是rn=1的时候执行速度比另外两个快的原因。
由于谓词信息为<=而不是=，猜想由于推入了rn导致在临时表空间中（10046event='direct path write temp'，file number=1001）操作的数据量变少。
也就是说rn的值推入后，根据值的不同控制了在temp文件中物理IO的量的多少，决定了WINDOW SORT PUSHED RANK的速度导致整体SQL的执行速度不同。
后续想着消除临时表空间的物理IO在看看效果，结果啥也不说了，水太深了，给组数据对比就知道了。
如何消除看我之前写的：Oracle手工增加排序区避免SQL使用临时表空间排序产生物理IO
15:58:22 SYS@xxxxxx1(1893)> ALTER SESSION SET workarea_size_policy = MANUAL; Session altered. Elapsed: 00:00:00.01 16:02:38 SYS@xxxxxx(1893)> ALTER SESSION SET sort_area_size = 1258291200; Session altered. Elapsed: 00:00:00.00
数据比对：
------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------- rn=1 |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 2798K|00:00:08.52 | 56921 | 486M| 7266K| 432M (0)| rn=0 |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 3144K|00:00:07.78 | 56921 | 486M| 7266K| 432M (0)| rn=10000 |* 2 | WINDOW SORT PUSHED RANK| | 1 | 3093K| 3144K|00:00:07.87 | 56921 | 486M| 7266K| 432M (0)| -------------------------------------------------------------------------------------------------------------------------------------
直接把我猜想干翻了。
rn的值并不是决定性的影响因素，只能说是影响因素之一。。
现在避免物理IO后，实际上无论rn等于多少，都一样快了，而且都一样慢了（rn=1变得和其它一样慢。。）。
暂时没头绪了。
