Chinaunix首页 | 论坛 | 博客

dingjun123的blog

首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1473998
  • 博文数量： 182
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3946
  • 用 户 组： 普通用户
  • 注册时间： 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文（182）

文章存档

2025年（10）

2024年（27）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友
最近访客
推荐博文
相关博文
oracle 12c开始分页的三种写法及其下推裁剪优化

分类： Oracle

2025-04-06 21:37:39

oracle分页的三种写法及其下推裁剪优化：

1) 从12c开始，分页有新的fetch,offset的语法，可以和order by写一起，内部使用row_number/rank分析函数实现，with ties用rank实现
2) row_number分析函数从oracle 11g后就做了优化，可以类似普通分页写法那样，满足条件可以下推裁剪，减少数据访问量
   rank分析函数可以下推
   
   注意：dense_rank不能走索引下推优化
3）传统rownum写法裁剪优化

12c fetch语法结构图：


点击(此处)折叠或打开

  1. SELECT column1, column2, ...
  2. FROM table
  3. [WHERE conditions]
  4. [GROUP BY columns]
  5. [HAVING conditions]
  6. [ORDER BY columns [ASC | DESC]]
  7. [OFFSET offset_value {ROW | ROWS}]
  8. [FETCH {FIRST | NEXT} [num | percent PERCENT] {ROW | ROWS} {ONLY | WITH TIES}];

其中offset是从哪个位置开始，跳过这个位置，开始从下个位置获取
fetch后面的first和next其实一样，只不过如果获取前n行，使用fetch first 10 rows only可读性更新
offset 5 rows fetch next 10 rows可读性更强，next和offset连用可读性更强
row和rows随便，一般用rows,only也可以不用，with ties类似rank排名,使用with ties包含重复排名,行数不会变多：


点击(此处)折叠或打开

  1. select object_name,rn
  2. from(
  3. select object_name,row_number() over(order by object_id desc) rn
  4. from b
  5. ) where rn<10;

从执行计划看ID=4，走索引的时候就裁剪了，只访问了10行：


点击(此处)折叠或打开

  1. Plan hash value: 1231498170

  3. -------------------------------------------------------------------------------------------------
  4. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  5. -------------------------------------------------------------------------------------------------
  6. | 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 5 |
  7. |* 1 | VIEW | | 1 | 10 | 9 |00:00:00.01 | 5 |
  8. |* 2 | WINDOW NOSORT STOPKEY | | 1 | 9 | 10 |00:00:00.01 | 5 |
  9. | 3 | TABLE ACCESS BY INDEX ROWID| B | 1 | 73318 | 10 |00:00:00.01 | 5 |
  10. | 4 | INDEX FULL SCAN DESCENDING| IDX_B | 1 | 9 | 10 |00:00:00.01 | 3 |
  11. -------------------------------------------------------------------------------------------------

  13. Predicate Information (identified by operation id):
  14. ---------------------------------------------------

  16.    1 - filter("RN"<10)
  17.    2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<10)

rank可下推，fetch with ties就用rank实现：


点击(此处)折叠或打开

  1. select object_name,rn from( select object_name,
  2. rank() over(order by object_id desc) rn from b ) where rn<10;

可以看到从ID=4走索引裁剪：

点击(此处)折叠或打开

  1. Plan hash value: 1231498170

  3. -------------------------------------------------------------------------------------------------
  4. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  5. -------------------------------------------------------------------------------------------------
  6. | 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 5 |
  7. |* 1 | VIEW | | 1 | 9 | 9 |00:00:00.01 | 5 |
  8. |* 2 | WINDOW NOSORT STOPKEY | | 1 | 9 | 10 |00:00:00.01 | 5 |
  9. | 3 | TABLE ACCESS BY INDEX ROWID| B | 1 | 73318 | 10 |00:00:00.01 | 5 |
  10. | 4 | INDEX FULL SCAN DESCENDING| IDX_B | 1 | 9 | 10 |00:00:00.01 | 3 |
  11. -------------------------------------------------------------------------------------------------

  13. Predicate Information (identified by operation id):
  14. ---------------------------------------------------

  16.    1 - filter("RN"<10)
  17.    2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<10)

dense_rank不能走索引下推优化，从执行计划看走全表扫描：


点击(此处)折叠或打开

  1. select object_name,rn from( select object_name,
  2. dense_rank() over(order by object_id desc) rn from b ) where rn<10;

  4. SQL_ID 5tfy82nhgwn31, child number 0
  5. -------------------------------------
  6. select object_name,rn from( select object_name, dense_rank() over(order
  7. by object_id desc) rn from b ) where rn<10

  9. Plan hash value: 3571113929

  11. ----------------------------------------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
  13. ----------------------------------------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.03 | 1428 | | | |
  15. |* 1 | VIEW | | 1 | 73318 | 9 |00:00:00.03 | 1428 | | | |
  16. |* 2 | WINDOW SORT PUSHED RANK| | 1 | 73318 | 10 |00:00:00.03 | 1428 | 2675K| 740K| 2377K (0)|
  17. | 3 | TABLE ACCESS FULL | B | 1 | 73318 | 73287 |00:00:00.01 | 1428 | | | |
  18. ----------------------------------------------------------------------------------------------------------------------

  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------

  23.    1 - filter("RN"<10)
  24.    2 - filter(DENSE_RANK() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<10)

分析函数分页的between and 写法也可以下推，先下推<=：


点击(此处)折叠或打开

  1. select object_name,rn from( select object_name,
  2. row_number() over(order by object_id desc) rn from b ) where rn between 5 and 10;

  4. SQL_ID f7vs81xvw5513, child number 0
  5. -------------------------------------
  6. select object_name,rn from( select object_name, row_number() over(order
  7. by object_id desc) rn from b ) where rn between 5 and 10

  9. Plan hash value: 1231498170

  11. -------------------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  13. -------------------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 5 |
  15. |* 1 | VIEW | | 1 | 10 | 6 |00:00:00.01 | 5 |
  16. |* 2 | WINDOW NOSORT STOPKEY | | 1 | 10 | 10 |00:00:00.01 | 5 |
  17. | 3 | TABLE ACCESS BY INDEX ROWID| B | 1 | 73318 | 10 |00:00:00.01 | 5 |
  18. | 4 | INDEX FULL SCAN DESCENDING| IDX_B | 1 | 10 | 10 |00:00:00.01 | 3 |
  19. -------------------------------------------------------------------------------------------------

  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------

  24.    1 - filter(("RN">=5 AND "RN"<=10))
  25.    2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=10)

传统rownum分页写法，如果只取top n,
必须是先排序，然后中间rownum<=并取rownum rn别名，然后rownum<
如果是between and ...必须三层嵌套，
{BANNED}最佳外层rn>...


点击(此处)折叠或打开

  1. select object_name,rn
  2. from(
  3. select object_name,rownum rn
  4. from(
  5. select object_name from b order by object_id desc
  6. ) where rownum<=10
  7. ) where rn >= 5;

  9. Plan hash value: 3182714800

  11. --------------------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  13. --------------------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 5 |
  15. |* 1 | VIEW | | 1 | 10 | 6 |00:00:00.01 | 5 |
  16. |* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 |
  17. | 3 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 |
  18. | 4 | TABLE ACCESS BY INDEX ROWID| B | 1 | 73318 | 10 |00:00:00.01 | 5 |
  19. | 5 | INDEX FULL SCAN DESCENDING| IDX_B | 1 | 10 | 10 |00:00:00.01 | 3 |
  20. --------------------------------------------------------------------------------------------------

  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------

  25.    1 - filter("RN">=5)
  26.    2 - filter(ROWNUM<=10)

使用12c新语法一样可以下推，实际上就是用分析函数实现的：


点击(此处)折叠或打开

  1. select object_name from b order by object_id desc offset 4 rows fetch next 6 rows only;
  2. Plan hash value: 1231498170

  4. -------------------------------------------------------------------------------------------------
  5. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  6. -------------------------------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 5 |
  8. |* 1 | VIEW | | 1 | 10 | 6 |00:00:00.01 | 5 |
  9. |* 2 | WINDOW NOSORT STOPKEY | | 1 | 10 | 10 |00:00:00.01 | 5 |
  10. | 3 | TABLE ACCESS BY INDEX ROWID| B | 1 | 73318 | 10 |00:00:00.01 | 5 |
  11. | 4 | INDEX FULL SCAN DESCENDING| IDX_B | 1 | 10 | 10 |00:00:00.01 | 3 |
  12. -------------------------------------------------------------------------------------------------

  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------

  17.    1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=10 AND
  18.               "from$_subquery$_002"."rowlimit_$$_rownumber">4))
  19.    2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=10)

with ties,可能返回多于5行，用rank实现，可以下推：


点击(此处)折叠或打开

  1. select object_name from b order by object_id desc fetch first 5 rows with ties;

  3. Plan hash value: 1231498170

  5. -------------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  7. -------------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 5 |
  9. |* 1 | VIEW | | 1 | 5 | 5 |00:00:00.01 | 5 |
  10. |* 2 | WINDOW NOSORT STOPKEY | | 1 | 5 | 5 |00:00:00.01 | 5 |
  11. | 3 | TABLE ACCESS BY INDEX ROWID| B | 1 | 73318 | 5 |00:00:00.01 | 5 |
  12. | 4 | INDEX FULL SCAN DESCENDING| IDX_B | 1 | 5 | 5 |00:00:00.01 | 3 |
  13. -------------------------------------------------------------------------------------------------

  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------

  18.    1 - filter("from$_subquery$_002"."rowlimit_$$_rank"<=5)
  19.    2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=5)






阅读(13) | 评论(0) | 转发(0) |
0

上一篇：MySQL的rows_examined指标缺陷总结

下一篇：没有了

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6