窗口功能 SORT 贵，我们可以克服它们吗？

我的要求：

决定 10 最好的帐户 a
DEPT_NUM

, 按帐号按升序订购。

询问

:


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;


Trace

:


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

他说，
INDEX STORAGE

在列上
DEPT_NUM

.

强迫
Full Table

扫描棒OT 3855 到 11092

表中的行总行数是 632667;

上述所有的 - 这些是测试区的结果。 生产实际上具有两倍的体积。

我的数据库 - exadata，季度 RAC. 在职的 Oracle 11g R2. 数据库足够强大，可以立即执行它，但是 DBA 不愿意 tempSpc 13米。 经营报告本报告的频率将是 4 每小时的时间。 最重要的是，这张表可以获得很多插入物/实时更新

我们可以这么做这个过程吗？

1/ 增加 PGA 会议？/不确定它是否真的是可能的？/

2/ 额外的索引是否有帮助？

我只是希望其他眼睛看它，因为我们的小组完全集中在参数上 DBA.

谢谢你的任何建议！
2021-03-20 添加评论
分享

没有找到相关结果

    已邀请:

    冰洋

    赞同来自:

    分析功能的性能可能取决于索引列的顺序。 改变指数C.
    /ACCTNUM,DEPT_NUM/

    在
    /DEPT_NUM,ACCTNUM/

    它可以降低成本并消除对临时表空间的需求。


    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 FULL SCAN 使用较慢的单块 IO, 但它按顺序返回数据并避免排序。

    示例方案和数据


    --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/ = 排序窗口 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/ = 窗户 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/
    2021-03-20 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    Copyright © 2023, All Rights Reserved