![129e67936ae7d110d124057e09afd4e6.png](https://i-blog.csdnimg.cn/blog_migrate/941049bd78593c717c82ce7147919120.jpeg)
在某个任务的开发中,需要实现一个对oracle数据表进行排序后拆分的功能。例如对用户表 USER,其有一个 USERID 列表示用户唯一编号,USERID 列为数字类型,有索引,不会重复,但不一定连续。现在要对 USERID 排序,然后平均分为4份,因此我们必须获得排序后,每一份数据的USERID的起始值。
一般来说,我们首先会想到使用 RowNum 来实现这一目的。众所周知,由于 Oracle 查询时,是先赋 RowNum ,然后排序,因此必须使用多层嵌套查询来实现此目标:
(这里必须吐槽一下知乎的文章中只要有英文的SELECT,文章就无法保存,因此下文中的SQL语句均使用了中文全角字符SELECT)
SELECT USERID FROM (
SELECT ROWNUM RN, USERID FROM (
SELECT USERID FROM USER ORDER BY USERID
)
) WHERE ROWNUM = 10000;
我用这种方法,在一个数据量约为 1.616 亿的表上执行,需要 25 分钟。
有没有更好的方法呢?实际上,Oracle 提供了一个 Row_Number() 函数,可以更高效的实现这个目标。
Row_Number() 函数用于对为每一条分组排序记录返回一个从1开始的序号,这样就可以方便的找到指定序号的记录了。例如下面的语句:
SELECT USERID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY USERID) RN, USERID FROM USER
) WHERE RN = 10000;
用这个方法,根据 WHERE 条件中的值不同,执行时间也不一样,WHERE 条件中的值排序越靠前,执行时间越小。在与之前相同的1.616 亿数据量的表上执行,当 WHERE RN = 100时,几十个毫秒即可执行完毕,当 RN = 10000000,只需 1分33秒,当 RN = 接近数据排序末尾时,用了25分18秒。作为对比,前一种多层查询的方法,无论 RN=多少,执行时间都是近似的。
注意使用上述两种方法时,需查看一下语句的执行计划。如果执行计划显示使用了全表扫描“TABLE ACCESS FULL”,则执行效率时比较差的。详细情况请看下一篇文章。