查看: 5630|回复: 23

[SQL] 【讨论】SQL语句外层套一层分页语句后,为什么性能急剧下降。请大神指点一下。

[复制链接]
论坛徽章:
2
优秀写手
日期:2015-01-01 06:00:20优秀写手
日期:2015-02-12 06:00:14
发表于 2014-12-30 10:38 | 显示全部楼层 |阅读模式
我有一句比较复杂的统计SQL语句,单独执行时间约2s。
假设这句SQL语句为A。

我的分页写法:

select p.* from (
    select z.*,ROWNUM as rowIndex from (
        A
    ) z
) p where p.rowIndex>=1 and p.rowIndex<=50

以上的分页SQL语句执行时间超过25s。

很费解,为什么前后性能会相差这么多?
外面加了一层分页语法后,是不是改变了原来的执行计划?

请大家帮我分析一下。有必要的话,我可以把真实SQL语句发上来。
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2014-12-30 10:59 | 显示全部楼层
select count(*) from (A)
需要几秒才能执行完?

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2014-12-30 11:10 | 显示全部楼层
2次不同语句的执行计划贴一下

使用道具 举报

回复
认证徽章
论坛徽章:
169
SQL数据库编程大师
日期:2016-01-13 10:30:43SQL极客
日期:2013-12-09 14:13:35SQL大赛参与纪念
日期:2013-12-06 14:03:45最佳人气徽章
日期:2015-03-19 09:44:03现任管理团队成员
日期:2015-08-26 02:10:00秀才
日期:2015-07-28 09:12:12举人
日期:2015-07-13 15:30:15进士
日期:2015-07-28 09:12:58探花
日期:2015-07-28 09:12:58榜眼
日期:2015-08-18 09:48:03
发表于 2014-12-30 12:23 | 显示全部楼层
求计划大开眼界

使用道具 举报

回复
认证徽章
论坛徽章:
3
蛋疼蛋
日期:2012-12-21 12:39:592013年新春福章
日期:2013-02-25 14:51:242013年新春福章
日期:2013-04-08 17:42:48
发表于 2014-12-30 13:16 | 显示全部楼层
分页写法有问题。  当嵌套查询超过一层之后, 外边的谓词没法推进到最里边的查询中。
试试下面的这个:
select p.* from (
    select z.*,ROWNUM as rowIndex from (
        A
    ) z where rownum  <=50
) p where p.rowIndex>=1

使用道具 举报

回复
论坛徽章:
2
优秀写手
日期:2015-01-01 06:00:20优秀写手
日期:2015-02-12 06:00:14
 楼主| 发表于 2014-12-30 13:36 | 显示全部楼层
lastwinner 发表于 2014-12-30 10:59
select count(*) from (A)
需要几秒才能执行完?

select count(*) from (
需要话费28秒。

使用道具 举报

回复
论坛徽章:
2
优秀写手
日期:2015-01-01 06:00:20优秀写手
日期:2015-02-12 06:00:14
 楼主| 发表于 2014-12-30 13:43 | 显示全部楼层
Naldonado 发表于 2014-12-30 12:23
求计划大开眼界

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 525833090
--------------------------------------------------------------------------------
| Id  | Operation                                         | Name
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |
|*  1 |  VIEW                                             |
|   2 |   COUNT                                           |
|   3 |    VIEW                                           |
|   4 |     COUNT                                         |
|   5 |      NESTED LOOPS OUTER                           |
|   6 |       VIEW                                        |
|   7 |        SORT ORDER BY                              |
|   8 |         VIEW                                      |
|   9 |          UNION-ALL                                |
|  10 |           SORT GROUP BY NOSORT                    |
|  11 |            TABLE ACCESS BY INDEX ROWID            | DM_TYDM
|* 12 |             INDEX UNIQUE SCAN                     | PK_DM_TYDM
|  13 |           SORT GROUP BY NOSORT                    |
|  14 |            TABLE ACCESS BY INDEX ROWID            | DM_TYDM
|* 15 |             INDEX UNIQUE SCAN                     | PK_DM_TYDM
|* 16 |           FILTER                                  |
|* 17 |            HASH JOIN OUTER                        |
|  18 |             VIEW                                  |
|  19 |              HASH GROUP BY                        |
|  20 |               VIEW                                |
|* 21 |                HASH JOIN RIGHT OUTER              |
|  22 |                 TABLE ACCESS FULL                 | FP_RZ_DM_YWLX
|* 23 |                 HASH JOIN RIGHT OUTER             |
|  24 |                  TABLE ACCESS BY INDEX ROWID      | T_NSRXX
|* 25 |                   INDEX RANGE SCAN                | INDEX_T_NSRXX_ZZGSBM
|* 26 |                  HASH JOIN                        |
|* 27 |                   HASH JOIN                       |
|* 28 |                    TABLE ACCESS FULL              | FP_RZ_DM_QDLXJL
|  29 |                    TABLE ACCESS FULL              | FP_RZ_QDLXYWLXGLB
|  30 |                   TABLE ACCESS BY INDEX ROWID     | FP_RZ_YWDJB
|  31 |                    BITMAP CONVERSION TO ROWIDS    |
|  32 |                     BITMAP AND                    |
|  33 |                      BITMAP CONVERSION FROM ROWIDS|
|* 34 |                       INDEX RANGE SCAN            | INDEX_FP_RZ_YWDJB_ZZ
|  35 |                      BITMAP CONVERSION FROM ROWIDS|
|  36 |                       SORT ORDER BY               |
|* 37 |                        INDEX RANGE SCAN           | INDEX_FP_RZ_YWDJB_YW
|  38 |             VIEW                                  |
|  39 |              HASH GROUP BY                        |
|  40 |               TABLE ACCESS FULL                   | FP_RZ_LMSYWDJMX
|  41 |           SORT GROUP BY NOSORT                    |
|  42 |            TABLE ACCESS BY INDEX ROWID            | DM_TYDM
|* 43 |             INDEX UNIQUE SCAN                     | PK_DM_TYDM
|  44 |           SORT GROUP BY NOSORT                    |
|  45 |            TABLE ACCESS BY INDEX ROWID            | DM_TYDM
|* 46 |             INDEX UNIQUE SCAN                     | PK_DM_TYDM
|* 47 |           HASH JOIN                               |
|  48 |            VIEW                                   |
|  49 |             HASH GROUP BY                         |
|  50 |              VIEW                                 |
|* 51 |               HASH JOIN RIGHT OUTER               |
|  52 |                TABLE ACCESS FULL                  | FP_RZ_DM_YWLX
|* 53 |                HASH JOIN RIGHT OUTER              |
|  54 |                 TABLE ACCESS BY INDEX ROWID       | T_NSRXX
|* 55 |                  INDEX RANGE SCAN                 | INDEX_T_NSRXX_ZZGSBM
|* 56 |                 HASH JOIN                         |
|* 57 |                  HASH JOIN                        |
|* 58 |                   TABLE ACCESS FULL               | FP_RZ_DM_QDLXJL
|  59 |                   TABLE ACCESS FULL               | FP_RZ_QDLXYWLXGLB
|  60 |                  TABLE ACCESS BY INDEX ROWID      | FP_RZ_YWDJB
|  61 |                   BITMAP CONVERSION TO ROWIDS     |
|  62 |                    BITMAP AND                     |
|  63 |                     BITMAP CONVERSION FROM ROWIDS |
|* 64 |                      INDEX RANGE SCAN             | INDEX_FP_RZ_YWDJB_ZZ
|  65 |                     BITMAP CONVERSION FROM ROWIDS |
|  66 |                      SORT ORDER BY                |
|* 67 |                       INDEX RANGE SCAN            | INDEX_FP_RZ_YWDJB_YW
|  68 |            TABLE ACCESS FULL                      | FP_RZ_LMSYWDJMX
|  69 |       VIEW                                        |
|  70 |        SORT GROUP BY                              |
|  71 |         NESTED LOOPS                              |
|  72 |          NESTED LOOPS                             |
|* 73 |           HASH JOIN                               |
|  74 |            TABLE ACCESS FULL                      | FP_RZ_FPJLYWDJGLB
|  75 |            VIEW                                   | INFO_FP_VIEW
|  76 |             UNION-ALL                             |
|  77 |              TABLE ACCESS FULL                    | INFO_ZZSFP
|  78 |              TABLE ACCESS FULL                    | INFO_YSFP
|* 79 |           INDEX UNIQUE SCAN                       | PK_T_NSRXX
|  80 |          TABLE ACCESS BY INDEX ROWID              | T_NSRXX
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("AUTOROWNO">=1 AND "AUTOROWNO"<=20)
  12 - access("DMLX"='djzt' AND "DM"=:B1)
  15 - access("DMLX"='qdqk' AND "DM"=:B1)
  16 - filter("LM"."DJBH" IS NULL)
  17 - access("LM"."DJBH"(+)="J"."DJBH")
  21 - access("L"."YWLX_DM"(+)="Y"."YWLX_DM")
  23 - access("T"."ZZGSBM"(+)="Y"."ZZGSBM")
  25 - access("T"."ZZGSBM"(+)='CDZ0000')
  26 - access("D"."YWLX_DM"="Y"."YWLX_DM")
  27 - access("D"."QDLX_DM"="Q"."QDLX_DM")
  28 - filter("Q"."SFJK"='Y')
  34 - access("Y"."ZZGSBM"='CDZ0000')
  37 - access("Y"."YWRQ">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:s
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  43 - access("DMLX"='djzt' AND "DM"=:B1)
  46 - access("DMLX"='qdqk' AND "DM"=:B1)
  47 - access("LM"."DJBH"="J"."DJBH")
  51 - access("L"."YWLX_DM"(+)="Y"."YWLX_DM")
  53 - access("T"."ZZGSBM"(+)="Y"."ZZGSBM")
  55 - access("T"."ZZGSBM"(+)='CDZ0000')
  56 - access("D"."YWLX_DM"="Y"."YWLX_DM")
  57 - access("D"."QDLX_DM"="Q"."QDLX_DM")
  58 - filter("Q"."SFJK"='Y')
  64 - access("Y"."ZZGSBM"='CDZ0000')
  67 - access("Y"."YWRQ">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:s
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  73 - access("F"."INDEX_NO"="FG"."FPID")
       filter("T0"."DJ_FPHM"="F"."FPHM" OR "T0"."DJID"="FG"."DJID")
  79 - access("F"."NSRSBH"="FN"."NSRSBH")

120 rows selected

使用道具 举报

回复
论坛徽章:
2
优秀写手
日期:2015-01-01 06:00:20优秀写手
日期:2015-02-12 06:00:14
 楼主| 发表于 2014-12-30 13:49 | 显示全部楼层
本帖最后由 carl_ 于 2014-12-30 13:50 编辑
bell6248 发表于 2014-12-30 11:10
2次不同语句的执行计划贴一下

这是不加分页的执行计划:(加了分页后的执行计划,请看回复Naldonado的那段)

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 313052727
--------------------------------------------------------------------------------
| Id  | Operation                                                      | Name
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                         |
|   1 |  COUNT                                                            |
|   2 |   NESTED LOOPS OUTER                                  |
|   3 |    VIEW                                                            |
|   4 |     SORT ORDER BY                                           |
|   5 |      VIEW                                                          |
|   6 |       UNION-ALL                                                |
|   7 |        SORT GROUP BY NOSORT                         |
|   8 |         TABLE ACCESS BY INDEX ROWID            | DM_TYDM
|*  9 |          INDEX UNIQUE SCAN                            | PK_DM_TYDM
|  10 |        SORT GROUP BY NOSORT                        |
|  11 |         TABLE ACCESS BY INDEX ROWID           | DM_TYDM
|* 12 |          INDEX UNIQUE SCAN                           | PK_DM_TYDM
|* 13 |        FILTER                                                   |
|* 14 |         HASH JOIN OUTER                                |
|  15 |          VIEW                                                    |
|  16 |           HASH GROUP BY                                  |
|  17 |            VIEW                                                  |
|* 18 |             HASH JOIN RIGHT OUTER                 |
|  19 |              TABLE ACCESS FULL                         | FP_RZ_DM_YWLX
|* 20 |              HASH JOIN RIGHT OUTER                 |
|  21 |               TABLE ACCESS BY INDEX ROWID      | T_NSRXX
|* 22 |                INDEX RANGE SCAN                       | INDEX_T_NSRXX_ZZGSBM
|* 23 |               HASH JOIN                                      |
|* 24 |                HASH JOIN                       |
|* 25 |                 TABLE ACCESS FULL                      | FP_RZ_DM_QDLXJL
|  26 |                 TABLE ACCESS FULL                       | FP_RZ_QDLXYWLXGLB
|  27 |                TABLE ACCESS BY INDEX ROWID     | FP_RZ_YWDJB
|  28 |                 BITMAP CONVERSION TO ROWIDS    |
|  29 |                  BITMAP AND                                         |
|  30 |                   BITMAP CONVERSION FROM ROWIDS  |
|* 31 |                    INDEX RANGE SCAN                           | INDEX_FP_RZ_YWDJB_ZZGSB
|  32 |                   BITMAP CONVERSION FROM ROWIDS  |
|  33 |                    SORT ORDER BY                                  |
|* 34 |                     INDEX RANGE SCAN                          | INDEX_FP_RZ_YWDJB_YWRQ
|  35 |          VIEW                                                             |
|  36 |           HASH GROUP BY                                           |
|  37 |            TABLE ACCESS FULL                                   | FP_RZ_LMSYWDJMX
|  38 |        SORT GROUP BY NOSORT                                 |
|  39 |         TABLE ACCESS BY INDEX ROWID                    | DM_TYDM
|* 40 |          INDEX UNIQUE SCAN                                      | PK_DM_TYDM
|  41 |        SORT GROUP BY NOSORT                                    |
|  42 |         TABLE ACCESS BY INDEX ROWID                   | DM_TYDM
|* 43 |          INDEX UNIQUE SCAN                                    | PK_DM_TYDM
|* 44 |        HASH JOIN                               |
|  45 |         VIEW                                   |
|  46 |          HASH GROUP BY                         |
|  47 |           VIEW                                 |
|* 48 |            HASH JOIN RIGHT OUTER                                 |
|  49 |             TABLE ACCESS FULL                                        | FP_RZ_DM_YWLX
|* 50 |             HASH JOIN RIGHT OUTER                                |
|  51 |              TABLE ACCESS BY INDEX ROWID                    | T_NSRXX
|* 52 |               INDEX RANGE SCAN                                      | INDEX_T_NSRXX_ZZGSBM
|* 53 |              HASH JOIN                                                     |
|* 54 |               HASH JOIN                                                    |
|* 55 |                TABLE ACCESS FULL                                    | FP_RZ_DM_QDLXJL
|  56 |                TABLE ACCESS FULL                                     | FP_RZ_QDLXYWLXGLB
|  57 |               TABLE ACCESS BY INDEX ROWID                   | FP_RZ_YWDJB
|  58 |                BITMAP CONVERSION TO ROWIDS                |
|  59 |                 BITMAP AND                                                 |
|  60 |                  BITMAP CONVERSION FROM ROWIDS         |
|* 61 |                   INDEX RANGE SCAN                                  | INDEX_FP_RZ_YWDJB_ZZGSB
|  62 |                  BITMAP CONVERSION FROM ROWIDS         |
|  63 |                   SORT ORDER BY                                         |
|* 64 |                    INDEX RANGE SCAN                                 | INDEX_FP_RZ_YWDJB_YWRQ
|  65 |         TABLE ACCESS FULL                                            | FP_RZ_LMSYWDJMX
|  66 |    VIEW                                                                        |
|  67 |     SORT GROUP BY                              |
|  68 |      NESTED LOOPS                              |
|  69 |       NESTED LOOPS                             |
|* 70 |        HASH JOIN                               |
|  71 |         TABLE ACCESS FULL                      | FP_RZ_FPJLYWDJGLB
|  72 |         VIEW                                             | INFO_FP_VIEW
|  73 |          UNION-ALL                                   |
|  74 |           TABLE ACCESS FULL                    | INFO_ZZSFP
|  75 |           TABLE ACCESS FULL                    | INFO_YSFP
|* 76 |        INDEX UNIQUE SCAN                     | PK_T_NSRXX
|  77 |       TABLE ACCESS BY INDEX ROWID     | T_NSRXX
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   9 - access("DMLX"='djzt' AND "DM"=:B1)
  12 - access("DMLX"='qdqk' AND "DM"=:B1)
  13 - filter("LM"."DJBH" IS NULL)
  14 - access("LM"."DJBH"(+)="J"."DJBH")
  18 - access("L"."YWLX_DM"(+)="Y"."YWLX_DM")
  20 - access("T"."ZZGSBM"(+)="Y"."ZZGSBM")
  22 - access("T"."ZZGSBM"(+)='CDZ0000')
  23 - access("D"."YWLX_DM"="Y"."YWLX_DM")
  24 - access("D"."QDLX_DM"="Q"."QDLX_DM")
  25 - filter("Q"."SFJK"='Y')
  31 - access("Y"."ZZGSBM"='CDZ0000')
  34 - access("Y"."YWRQ">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:s
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  40 - access("DMLX"='djzt' AND "DM"=:B1)
  43 - access("DMLX"='qdqk' AND "DM"=:B1)
  44 - access("LM"."DJBH"="J"."DJBH")
  48 - access("L"."YWLX_DM"(+)="Y"."YWLX_DM")
  50 - access("T"."ZZGSBM"(+)="Y"."ZZGSBM")
  52 - access("T"."ZZGSBM"(+)='CDZ0000')
  53 - access("D"."YWLX_DM"="Y"."YWLX_DM")
  54 - access("D"."QDLX_DM"="Q"."QDLX_DM")
  55 - filter("Q"."SFJK"='Y')
  61 - access("Y"."ZZGSBM"='CDZ0000')
  64 - access("Y"."YWRQ">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:s
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  70 - access("F"."INDEX_NO"="FG"."FPID")
       filter("T0"."DJ_FPHM"="F"."FPHM" OR "T0"."DJID"="FG"."DJID")
  76 - access("F"."NSRSBH"="FN"."NSRSBH")

116 rows selected

使用道具 举报

回复
论坛徽章:
2
优秀写手
日期:2015-01-01 06:00:20优秀写手
日期:2015-02-12 06:00:14
 楼主| 发表于 2014-12-30 13:56 | 显示全部楼层
yongzhi2008 发表于 2014-12-30 13:16
分页写法有问题。  当嵌套查询超过一层之后, 外边的谓词没法推进到最里边的查询中。
试试下面的这个:
s ...

你好,用你的方法试了。
效果很好,现在分页执行只需要1秒。

但我的SQL语句外面那层分页语句是框架里统一加的,需要调整框架了。

谢谢!

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2014-12-30 14:43 | 显示全部楼层
如果这样写呢?

    select z.*,ROWNUM as rowIndex from (
        A
    ) z where rownum>=1 and rownum<=50

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

DTCC2020中国数据库技术大会 限时9.5折

【架构革新 高效可控】2020年8月17日~19日第十一届中国数据库技术大会将在北京隆重召开。

大会设置2大主会场,20+技术专场,将邀请超百位行业专家,重点围绕数据架构、AI与大数据、传统企业数据库实践和国产开源数据库等内容展开分享和探讨,为广大数据领域从业人士提供一场年度盛会和交流平台。

http://dtcc.it168.com


大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表