原创文章,如需转载,请注明出处。
一.案例场景:
有一张order表如下,数据量是亿级别,对这个表的分页查询,如何根据userId条件查出第21条到第40条的数据。
id | userId | payStatus | gmtCreate |
1 | 100000001 | 1 | 2009-09-13 |
… | … | … | … |
200000000 | 100000001 | 1 | 2009-10-13 |
二.解决该问题的必备知识点:
1 rowid是oracle的一个内部值,对应一条记录的物理地址,通过rowid查找一条记录是最快的。
rownum是oracle给查询返回的结果集的行编号,第一行是1,第二行是2,rownum只能小于等于某个值,不能大于某个值。
2 Hint是oracle特有的语法,写在SQL注释中的一些提示,表示强制使用某些执行策略而忽视掉Oracle自己的优化方案。
3 use_nl表示use nest loop join。两个表,以第一个表为驱动表,拿出第1个表的第1条记录,去和第2张表关联,查询到需要的结果,再选取第1个表的第2条记录,再去关联,直到结束。 nest loop join非常适合一张很小的表和一张非常大的表的关联。
三. 解决方案:
1. 找出符合条件的所有记录的rowid和rownum。
select rowid as rid, rownum as rnum from order where userId=:1 orderby gmtCreate |
2. 将第1次查询的结果集作为一张临时表,从这张临时表中查出rownum<=40的记录。
select rid, rnum from (select rowid as rid, rownum as rnum from order where userId=:1 orderby gmtCreate) where rownum <=40 |
3. 将第2次查询的结果集作为一张临时表,从这张临时表中查出20<rownum<=40的记录的rowid。
select rid from (select rid, rnum from (select rowid as rid, rownum as rnum from order where userId=:1 orderby gmtCreate) where rownum <=40) where rn>20 |
4. 将得到的rowid的列表和tc_biz_order关联,关联条件是两个表rowid相同,就可以取得需要的所有字段。/*+ ordered use_nl(t1,t2)*/这个表示强制执行内嵌循环表(即use_nl(t1,t2))的优化策略。
select /*+ ordered use_nl(t1,t2)*/ t2.* from (select rid from (select rid, rnum from (select rowid as rid, rownum as rnum from order where userId=:1 orderby gmtCreate) where rownum <=40) where rn>20)t1, order t2 where t1.rid= t2.rowid |