取前50条排序的时候,用 oracle的分析函数就搞定了。row_number() over(order by accountDATE desc) rn 走的是索引扫描,只排出前N条,而且只取前N条记录。然后在外层取rn<51就ok了。
其实如果只是取top 50之类的很快,手头oracle没有这么大数据量,但正好有一个SQL Server表3千600万数据,测试一下:--为了准确,先清空Database buffer checkpoint dbcc freeproccache dbcc dropcleanbuffers --运行 SELECT count(1) FROM event WHERE RECEIVEDDATE>='2009-08-01' 返回结果数36230316 --看运行时间 SQL Server Execution Times: CPU time = 5593 ms, elapsed time = 123872 ms.123秒 ok,现在再运行一遍清空database buffercheckpoint dbcc freeproccache dbcc dropcleanbuffers然后再运行 SELECT TOP 50 * FROM event WHERE RECEIVEDDATE>='2009-08-01' ORDER BY RECEIVEDDATESQL Server parse and compile time: CPU time = 0 ms, elapsed time = 18 ms.(50 row(s) affected)SQL Server Execution Times: CPU time = 16 ms, elapsed time = 333 ms. 大家看看时间,不到1秒钟,top 其实很快的 另外我的receiveddate 建有聚族(clustered index)索引,我测试几遍都基本这个结果, 不知道测试过程有什么问题没有, 我想oracle应该也很快,手头有数据的可以测试法上来看看呀
我的表table1又800多万条记录。 用select * from table1 where rownum<order by column1 desc 花了55秒,sql如下: select * from (select *, rownum rn from (select * from table1 where rownum < order by column1 desc) a) b where b.rn < 101 and b.rn > 50;用 row_number() over(order by column1 desc)的方式花了0.512秒,sql如下: select * from (select *, row_number() over(order by column1 desc) rn from table1) a where a.rn < 101 and a.rn > 50
你的第一个语句写的有问题 select * from (select *, rownum rn from (select * from table1 order by column1 desc) where rownum<101 a) b where b.rn > 50;正常情况下这两种写的效率是一样的,因为同样要ORDER BY一下的
使用用 row_number() over(order by column1 desc)的方式row_number() over(order by accountDATE desc) rn 它走的是索引扫描,只排出前N条,而且只取前N条记录。
走的是索引扫描,只排出前N条,而且只取前N条记录。然后在外层取rn<51就ok了。
checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
--运行
SELECT count(1) FROM event WHERE RECEIVEDDATE>='2009-08-01'
返回结果数36230316
--看运行时间
SQL Server Execution Times:
CPU time = 5593 ms, elapsed time = 123872 ms.123秒
ok,现在再运行一遍清空database buffercheckpoint
dbcc freeproccache
dbcc dropcleanbuffers然后再运行
SELECT TOP 50 * FROM event WHERE RECEIVEDDATE>='2009-08-01'
ORDER BY RECEIVEDDATESQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 18 ms.(50 row(s) affected)SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 333 ms. 大家看看时间,不到1秒钟,top 其实很快的
另外我的receiveddate 建有聚族(clustered index)索引,我测试几遍都基本这个结果,
不知道测试过程有什么问题没有,
我想oracle应该也很快,手头有数据的可以测试法上来看看呀
用select * from table1 where rownum<order by column1 desc 花了55秒,sql如下:
select *
from (select *, rownum rn
from (select *
from table1
where rownum <
order by column1 desc) a) b
where b.rn < 101
and b.rn > 50;用 row_number() over(order by column1 desc)的方式花了0.512秒,sql如下:
select *
from (select *, row_number() over(order by column1 desc) rn from table1) a
where a.rn < 101
and a.rn > 50
select *
from (select *, rownum rn
from (select *
from table1
order by column1 desc)
where rownum<101
a) b
where b.rn > 50;正常情况下这两种写的效率是一样的,因为同样要ORDER BY一下的
它走的是索引扫描,只排出前N条,而且只取前N条记录。