1.Oracle 12c fetch抓取记录

查询前10行记录

# fetch first 10 rows only

查询6至10行记录

# offset 5 rows fetch next 5 rows only

按百分比查询

# offset 10 rows fetch next 10 percent rows only

 

 

2.利用伪列rownum嵌套分页抓取记录

select * from

(select rownum as rn,t1.* from where rownum <= 10) t2

where rn >= 5

注:效率最高,几乎不受影响,但是SQL主体和分页操作不分离,不利于程序开发

 

select * from

(select rownum as rn,t1.* from

(select * from test) t1

where rownum <= 10) t2

where rn >= 5

 

select * from

(

select rownum as rn,t1.* from

(select * from test) t1

)

where rn between 5 and 10

 

注:其中绿色的表示不进行翻页的原始语句,蓝色部分控制分页查询的范围

 

选择第6到10条记录存在两种方法，

一种是上面例子中展示的在查询的第二层通过ROWNUM <= 10来控制最大值，在查询的最外层控制最小值。

而另一种方式是去掉查询第二层的WHERE ROWNUM <= 10语句，在查询的最外层控制分页的最小值和最大值。

 

查询效率分析:

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

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

而第二个查询语句，由于查询条件BETWEEN 6 AND 10是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

 

以下主要为实现分页功能:

3.利用分析函数row_number()over(order by 字段)增加记录排序列

select * from(

select id,realname,row_number()over(order by id asc) rn from t_user

) where rn between 6 and 10

 

4.利用minus方法

(SELECT * FROM (SELECT * FROM test ORDER BY id asc) WHERE ROWNUM<10)

MINUS

(SELECT * FROM (SELECT * FROM test ORDER BY id asc) WHERE ROWNUM<6);

 

建议:

1.SQL主体不需要排序的简单操作,建议采用方式二的第一种方法

2.SQL主题需要排序且逻辑复杂,建议采用方式二中的第二种方法或者方式一(只支持Oracle 12C)