Oracle为什么要用三层嵌套实现分页

Oracle为什么要用三层嵌套实现分页

先说结论：

嵌套出两层SELECT
对应的两个原因：

• ROWNUM的取值是在数据库物理行号，不受ORDER BY 影响。如果存在同一趟SELECT中，order by排序后的ROWNUM不是有序的，不会和查询结果行号对应，所以不能做条件过滤。需要在一次select order by 后得到的虚拟表中，再获取ROWNUM这样order by才能生效。

• ROWNUM 只能做小于，不能直接做大于范围取值。

项目中使用分页插件，控制台输出sql 为:

1. 首先发现最外层嵌套只是为了做 WHERE row_id >0。 而 row_id 就是ROWNUM 别名。

是否可以减少一层，让 where 0 < ROWNUM <=10

会直接报错: ROWNUM 不能直接做大于范围取值。

解决办法: 套一层select , 在对rownum 取别名后, 可以进行大于范围取值

到此。解释了为什么要套第一层select

2. 接下来的问题： rownum 是在 order by 之前的排序 。

先来看，没有使用order by 排序时, rownum的顺序就是行号顺序

而同一趟SELECT 中同时取rownum 又 order by 时：

可以看出：ROWNUM的取值不受ORDER BY 影响,也就是先于ORDER BY之前, ROWNUM的值已经确定，是取得数据原本在数据库的行号。 看起来ROWNUM顺序混乱。
如果此时再使用rownum进行分页范围取值, 取到的数据并不是期望的按order by 的排序得到的范围。order by 不起作用，而往往用户都会指定order by的。

解决思路: 让order by 先执行, 得到顺序后, 再生成顺序号rownum , 再用rownum分页时就是按 order by 的顺序了.

想法一：

内层select 做完order by 排序, 再在外层 取rownum 做范围截取

---- 报错rownum列不存在，没有在返回值列表显示写出rownum就不会返回rownum。
而就算此时不会报错也嵌套了一层SELECT。

想法二:
只能让rownum 和 order by 分别位于不同层级, 并保证 order by 先执行。
再对order by 后得到的虚拟表取rownum就能拿到期望的顺序号了，再做 小于 范围取值。

注意：内层返回列表没有取rownum，如果取了rownum则仍然又拿到数据在数据库的物理行号。

还需注意：第三层对rownum 做大于范围取值时, 仍然要取别名（作为普通列）再做 大于 范围取值。

结合起来就实现了分页插件三层嵌套SELECT的效果。

总结：

多嵌套了两层SELECT ，对应的两个原因：

• ROWNUM的取值是在数据库物理行号，不受ORDER BY 影响。如果存在同一趟SELECT中，order by排序后的ROWNUM不是有序的，不会和查询结果行号对应，所以不能做条件过滤。需要在一次select order by 后得到的虚拟表中，再获取ROWNUM这样order by才能生效。

• ROWNUM 只能做小于，不能直接做大于范围取值。

三层SELECT分别的作用是：

• 第一层是为了做order_by (order_by 不能和ROWNUM范围取值一起使用)
• 第二层是为了做ROWNUM <=10，并且对ROWNUM取别名用于第三层(作普通列)做大于范围取值。
• 第三层做ROW__ID >0

同时细心我的发现了一个疑问，在最里层的SELECT语句中没有对查询数量做任何限制，不会查出全表数据吗？假分页？
对此的解答是：

CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。
对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40 就可以被Oracle推入到内层查询中。
这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

同理第三个SELECT 里的 WHERE row_id >0 是否也推到第二层。
这样看来虽然在编写sql时嵌套子查询，但Oracle悄悄优化掉了。