关于rownum排序分页会比分析函数排序快的问题。。。
来源:互联网 发布:java color rgb 编辑:程序博客网 时间:2024/04/27 21:02
测试过程:
create table test as select * from dba_objects;
insert into test select * form dba_objects;
……
commit;
create table sort_test as
select rownum rn,t.* from test t order by object_id;
SQL> select count(*) from sort_test;
COUNT(*)
----------
842752
create or replace procedure sp_test is
num1 number;
num2 number;
begin
runstats_pgk.rs_start;
for i in 1..100 loop
select count(*) into num1 from
(
select * from (
select rownum countnum,t.* from (
select * from sort_test order by object_id,rn) t where rownum<=50
) where countnum>0
);
end loop;
runstats_pgk.rs_middle;
for i in 1..100 loop
select count(*) into num2 from (
select * from (
select row_number() over(order by object_id,rn) countnum,t.* from sort_test t)
where countnum>0 and countnum<=50
);
end loop;
runstats_pgk.rs_stop;
end sp_test;
以下是测试的结果:
SQL> exec sp_test;
run1 ran in 21286 hsecs
run2 ran in 16932 hsecs
run1 ran in 125.71% if the time
Name Run1 Run2 Diff
LATCH.simulator lru latch 1 0 -1
STAT...change write time 1 0 -1
STAT...free buffer requested 4 5 1
LATCH.job_queue_processes para 4 2 -2
STAT...cleanout - number of kt 3 5 2
STAT...consistent gets - exami 3 5 2
STAT...consistent gets 1,093,003 1,093,005 2
STAT...active txn count during 3 5 2
STAT...calls to kcmgcs 3 5 2
LATCH.row cache objects 149 152 3
LATCH.row cache enqueue latch 144 148 4
STAT...consistent changes 509 515 6
STAT...db block gets 519 525 6
STAT...db block changes 1,018 1,025 7
STAT...session logical reads 1,093,522 1,093,530 8
LATCH.dml lock allocation 5 19 14
LATCH.hash table column usage 0 14 14
LATCH.lgwr LWN SCN 70 56 -14
LATCH.mostly latch-free SCN 70 56 -14
LATCH.session allocation 52 38 -14
LATCH.redo allocation 580 594 14
LATCH.active checkpoint queue 70 55 -15
LATCH.session timer 71 56 -15
LATCH.enqueue hash chains 254 236 -18
LATCH.undo global data 23 44 21
LATCH.channel operations paren 137 111 -26
LATCH.redo writing 220 180 -40
LATCH.enqueues 276 223 -53
LATCH.session idle bit 170 115 -55
STAT...redo size 64,880 64,940 60
LATCH.library cache pin alloca 112 178 66
LATCH.cache buffers chains 2,189,080 2,189,156 76
LATCH.shared pool 282 361 79
LATCH.library cache pin 537 627 90
LATCH.messages 511 407 -104
LATCH.simulator hash latch 69,249 69,120 -129
LATCH.library cache 655 800 145
LATCH.SQL memory manager worka 4,556 3,885 -671
LATCH.checkpoint queue latch 6,656 5,280 -1,376
STAT...recursive cpu usage 21,291 16,933 -4,358
STAT...session pga memory max 7,916 0 -7,916
run1 latchs total versus runs -- defference and pct
Run1 Run2 Diff Pct
2,273,958 2,271,937 -2,021 100%
PL/SQL 过程已成功完成。
SELECT count(*) from
(
select * from (
select rownum countnum,t.* from (
select * from sort_test order by object_id,rn) t where rownum<=50
) where countnum>0
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.00 0.00 0 0 0 0
Fetch 100 212.87 207.84 0 1093000 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 212.87 207.85 0 1093000 0 100
SELECT count(*) from (
select * from (
select row_number() over(order by object_id,rn) countnum,t.* from sort_test t)
where countnum>0 and countnum<=50
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.01 0.00 0 0 0 0
Fetch 100 169.29 165.32 0 1093000 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 169.30 165.33 0 1093000 0 100
- 关于rownum排序分页会比分析函数排序快的问题。。。
- rownum排序的问题
- Oracle 使用ROWNUM分页时的排序问题
- Oracle rownum 分页, 排序
- Oracle rownum 分页, 排序
- Oracle rownum 分页, 排序
- Oracle rownum 分页, 排序
- 关于oracle的rownum分页问题
- 关于jqgrid 分页 排序的问题
- 比快速排序还快的排序算法
- 基数排序比快速排序快
- ORACLE中用rownum分页并排序的SQL语句
- Oracle下用rownum进行分页时排序的错乱
- ORACLE中用rownum分页并排序的SQL语句
- ORACLE中用rownum分页并排序的SQL语句
- ORACLE中用ROWNUM分页并排序的SQL语句
- ygfmobileORACLE中用rownum分页并排序的SQL语句
- ORACLE中用rownum分页并排序的SQL语句
- 晕了,UCOS
- 流式flash格式——FLV
- 10021.skyeye
- 如何彻底删除软件程序(某些软件/程序在添加删除程序中不能删除)
- 急!急!!关于sql2000数据库增长量非常快,而库日志却不增长?
- 关于rownum排序分页会比分析函数排序快的问题。。。
- 狼的初帖!!!
- 看广告
- 痛苦呀,代码
- 最近项目进展
- 实现千万级数据的分页显示
- 解决内存溢出九法
- 光棍节……快乐……
- bugzilla 安装