java db2分页查询_三种数据库Oracle、MySQL、DB2分页查询

1、Oracle分页查询

select * from (select rownum,name from table where rownum <= endIndex) where rownum > startIndex

2、DB2分页查询

select * from (select col1,col2,col3,row_number() over(order by 排序用的列名 desc) as rownum) as a1 where a1.rownum between 10 and 20

较慢，下面经实验效率较高

select a.* from (select row_number() over(order by id) asrownum,username,email,phone,addressfrom user) a

where a.rownum>10 fetch first 20 rows only optimize for 20 rows

注意：在MyBatista中

fetch first 20 rows only optimize for 20 rows的参数没办法参数化，不能使用预编译指令 #{} or ##，所以使用 ${} or $$即可。

解释：fetch和optimize(由于翻译可能不准确，译文自行理解)

The DB2 data server does not automatically assume OPTIMIZE FOR n ROWS when FETCH FIRST n ROWS ONLY is specified for the outer subselect. Try specifying OPTIMIZE FOR n ROWS along with FETCH FIRST n ROWS ONLY, to encourage query access plans that return rows directly from the referenced tables, without first performing a buffering operation such as inserting into a temporary table, sorting, or inserting into a hash join hash table.

Applications that specify OPTIMIZE FOR n ROWS to encourage query access plans that avoid buffering operations, yet retrieve the entire result set, might experience poor performance. This is because the query access plan that returns the first n rows fastest might not be the best query access plan if the entire result set is being retrieved.

The OPTIMIZE FOR n ROWS clause indicates to the optimizer that the application intends to retrieve only n rows, but the query will return the complete result set. The FETCH FIRST n ROWS ONLY clause indicates that the query should return only n rows.

我理解的意思是optimize是向优化器指出只检索20行数据，避免缓冲操作造成性能不佳。fetch用于返回结果集前20行数据。

3、MySQL分页查询

select * from table limit start,pageNum