窗口功能SORT成本高昂，我们可以克服吗? [英] Window functions SORT costly, can we overcome it?
问题描述
我的要求:识别DEPT_NUM的前10个帐户，该帐户按帐号升序排列.
My Requirement: Identify top 10 accounts of a DEPT_NUM , ordered by the account number in ascending order.
查询:
SELECT * FROM
(
select acctnum,dept_num,row_number() OVER (PARTITION BY DEPT_NUM ORDER BY ACCTNUM) as row_identifier
FROM MYTABLE
)
WHERE row_identifier between 1 and 10;
跟踪:
7532 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1480074522
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 577K| 15M| | 3855 (1)| 00:00:47 |
|* 1 | VIEW | | 577K| 15M| | 3855 (1)| 00:00:47 |
|* 2 | WINDOW SORT PUSHED RANK| | 577K| 7890K| 13M| 3855 (1)| 00:00:47 |
| 3 | INDEX FAST FULL SCAN | IMTAB05 | 577K| 7890K| | 987 (1)| 00:00:12 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_IDENTIFIER">=1 AND "ROW_IDENTIFIER"<=5)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPT_NUM" ORDER BY "ACCTNUM")<=5)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
4298 consistent gets
0 physical reads
0 redo size
144367 bytes sent via SQL*Net to client
486 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7532 rows processed
索引:
index scan在列DEPT_NUM上说INDEX STORAGE.
强制Full Table扫描的费用从3855降低至11092
Forcing Full Table scan made cost from 3855 to 11092
表中的总行数为632667；
Total number of rows in the table is 632667;
以上所有均为测试区域结果.生产实际上是产量的两倍.
All the above are test region results. Production actually has twice the amount.
我的数据库是Exadata，Quarter RAC.运行Oracle 11g R2.该数据库功能强大到可以立即执行，但是DBA不愿使用13M的tempSpc.商业报告该报告的频率将是每小时4次.最主要的是，该表可以进行大量实时插入/更新
My Database is Exadata, Quarter RAC. Running Oracle 11g R2. The databse is powerful enough to execute it instantly, But DBA were reluctant on the tempSpc of 13M. Business reported the frequency of this report would be 4 times an hour. And Main thing is, this table gets a Lot of real time inserts/updates
我们可以像这样
即兴地完成这个过程吗?
1)增加会话的PGA?(不确定，是否真的可能吗?)
2)额外的索引会有帮助吗?
Can we improvise the process like
1) Increasing the PGA for a session?(Not sure, if it really possible?)
2) Will An additional index help?
只希望让其他人对此有所了解，因为我们小组完全专注于DBA参数.
Just want some different eyes to look on this, as our group is totally focusing on the DBA parameters only.
感谢任何建议！
推荐答案
分析功能的性能可能取决于索引列的顺序.将索引从(ACCTNUM,DEPT_NUM)更改为(DEPT_NUM,ACCTNUM)可以降低成本，并消除对临时表空间的需要.
Analytic function performance may depend on the index column order. Changing the index from (ACCTNUM,DEPT_NUM) to (DEPT_NUM,ACCTNUM) may lower the cost and remove the need for temporary tablespace.
partition by COL_2 order by COL_1 => INDEX FAST FULL SCAN|WINDOW SORT PUSHED RANK
partition by COL_1 order by COL_2 => INDEX FULL SCAN|WINDOW NOSORT
INDEX FAST FULL SCAN使用更快的多块IO，但它还需要对数据进行排序，并可能需要对排序区域进行临时表空间的排序.
INDEX FAST FULL SCAN uses faster multi-block IO but it also requires sorting the data and possibly temporary tablespace for the sort area.
INDEX FULL SCAN使用较慢的单块IO，但它按顺序返回数据并避免排序.
INDEX FULL SCAN uses slower single-block IO but it returns the data in order and avoids sorting.
示例架构和数据
--drop table mytable;
create table mytable(dept_num number not null, acctnum number not null
,a number, b number, c number, d number, e number);
insert into mytable
select 1 dept_num, 1 acctnum, 0,0,0,0,0 from dual union all
select 1 dept_num, 2 acctnum, 0,0,0,0,0 from dual union all
select 1 dept_num, 3 acctnum, 0,0,0,0,0 from dual union all
select 2 dept_num, 1 acctnum, 0,0,0,0,0 from dual union all
select 2 dept_num, 2 acctnum, 0,0,0,0,0 from dual union all
select 3 dept_num, 1 acctnum, 0,0,0,0,0 from dual;
--Create 600K similar rows.
insert into mytable
select dept_num + rownumber*3, acctnum, a,b,c,d,e
from mytable
cross join (select level rownumber from dual connect by level <= 100000);
begin
dbms_stats.gather_table_stats(user, 'mytable');
end;
/
(ACCTNUM，DEPT_NUM)= WINDOW SORT PUSHED RANK
create index mytable_idx on mytable(acctnum, dept_num);
explain plan for
select dept_num, acctnum from
(
select dept_num, acctnum
,row_number() over (partition by dept_num order by acctnum) as row_identifier
from mytable
)
where row_identifier between 1 and 10;
select * from table(dbms_xplan.display);
Plan hash value: 952182109
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 600K| 22M| | 1625 (3)| 00:00:23 |
|* 1 | VIEW | | 600K| 22M| | 1625 (3)| 00:00:23 |
|* 2 | WINDOW SORT PUSHED RANK| | 600K| 4687K| 9424K| 1625 (3)| 00:00:23 |
| 3 | INDEX FAST FULL SCAN | MYTABLE_IDX | 600K| 4687K| | 239 (3)| 00:00:04 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_IDENTIFIER">=1 AND "ROW_IDENTIFIER"<=10)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPT_NUM" ORDER BY "ACCTNUM")<=10)
(DEPT_NUM，ACCTNUM)= WINDOW NOSORT
drop index mytable_idx;
create index mytable_idx on mytable(dept_num, acctnum);
explain plan for
select dept_num, acctnum from
(
select dept_num, acctnum
,row_number() over (partition by dept_num order by acctnum) as row_identifier
from mytable
)
where row_identifier between 1 and 10;
select * from table(dbms_xplan.display);
Plan hash value: 1773829932
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 600K| 22M| 792 (2)| 00:00:12 |
|* 1 | VIEW | | 600K| 22M| 792 (2)| 00:00:12 |
|* 2 | WINDOW NOSORT | | 600K| 4687K| 792 (2)| 00:00:12 |
| 3 | INDEX FULL SCAN| MYTABLE_IDX | 600K| 4687K| 792 (2)| 00:00:12 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_IDENTIFIER">=1 AND "ROW_IDENTIFIER"<=10)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPT_NUM" ORDER BY
"ACCTNUM")<=10)
这篇关于窗口功能SORT成本高昂，我们可以克服吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
