例:
select * from(select a.*,row_number() over(order by a.tableid) as rn from t1 a where exists (select 1 from t2 b where b.tabschema=a.tabschema and b.tabname=a.tabname)) where rn>10 fetch first 10 rows only optimize for 10 rows
高效的分页算法:
1. order by列必须有索引 (上面的SQL指的是tableid列)
2. 如果多表关联, 关联列上必须要有索引 (t2表:tabschema,tabname列上必须有索引)
3. 分页限制条件: rn > :x fetch first N rows only optimize for N rows
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.
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.
DB2高效分页算法
原创
©著作权归作者所有:来自51CTO博客作者masaly的原创作品,请联系作者获取转载授权,否则将追究法律责任
上一篇:Tips for improving offline reorganization performance
下一篇:Slow connection when connecting from a DB2 Windows client
![](https://ucenter.51cto.com/images/noavatar_middle.gif)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
DB2日常运维命令linux sql Database db2
-
db2的sql分页 db2分页查询百万数据
BACKUP DATABASE TO dir | dev WITH num-buf BUFFERS BUFFER buff-size PARALLELISM n COMPRESS UTIL_IMPACT_PRIORITY priority1.&nbs
db2的sql分页 db2下海量数据分页查询的java实现 缓存 数据 表空间