ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 1514|回复: 6

大数据表的orderby 优化问题

[复制链接]
论坛徽章:
0
发表于 2017-1-12 13:02 | 显示全部楼层 |阅读模式
各位大神,请教个sql优化的问题,有四张表
DISTRIBUTE_MATCH_RESULT 数据量 20W
DISTRIBUTE_MATCH_ROLE   数据量50W
DISTRIBUTE_MATCH_NUSER 数据量1W
ACT_RU_PROCINST_EXTEND 数据量20W

下面SQL,正常执行就需要花费1-2S,如果去掉orderby 执行时间0.03秒所有,执行sql如下,执行计划也贴出来了,应该从哪方面去优化

  1. SELECT *  FROM (SELECT  A.*,ROWNUM RN
  2.       FROM (SELECT
  3.               DMR.TASK_ID,
  4.               ARPE.START_TIME                 AS TASK_CREATE_DATE,
  5.               DMR.TASK_PRIORITY,
  6.               DMR.WARN_FLAG                   AS TASK_WARN_FLAG,
  7.               ARPE.WARN_FLAG                  AS PROCESS_WARN_FLAG,
  8.               CASE WHEN NVL(DMR.WARN_FLAG, 0) >= NVL(ARPE.WARN_FLAG, 0)
  9.                 THEN NVL(DMR.WARN_FLAG, 0)
  10.               ELSE NVL(ARPE.WARN_FLAG, 0) END AS WARN_FLAG
  11.             FROM
  12.               DISTRIBUTE_MATCH_RESULT DMR INNER JOIN ACT_RU_PROCINST_EXTEND ARPE ON DMR.PROC_INST_ID = ARPE.PROC_INST_ID
  13.             WHERE EXISTS(SELECT 1
  14.                          FROM DISTRIBUTE_MATCH_ROLE R
  15.                          WHERE DMR.TASK_ID = R.TASK_ID AND R.ROLE_ID IN ('ceshi','UWBPS','tjtest'))
  16.                   AND DMR.TASK_STATE = '1'
  17.                   AND NOT EXISTS(SELECT 1
  18.                              FROM DISTRIBUTE_MATCH_NUSER DMN
  19.                              WHERE DMN.TASK_ID = DMR.TASK_ID AND DMN.CLERK_NO = 'tj0010')
  20.                   AND DMR.DISTRIBUTE_STAGE='RR'
  21.                   AND DMR.BRANCH_NO = '120000'
  22.                   AND DMR.IS_DELETED = '0'
  23.                   AND ARPE.PROCESS_SUSPEND_STATE = '1'
  24.             ORDER BY TASK_PRIORITY DESC, WARN_FLAG DESC, TASK_CREATE_DATE ASC) A
  25.       WHERE ROWNUM <= 10)
  26. WHERE RN >= 1;
复制代码


-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                                |    10 |   780 |       |  6963   (1)| 00:01:24 |
|*  1 |  VIEW                              |                                |    10 |   780 |       |  6963   (1)| 00:01:24 |
|*  2 |   COUNT STOPKEY                    |                                |       |       |       |            |          |
|   3 |    VIEW                            |                                | 45022 |  2857K|       |  6963   (1)| 00:01:24 |
|*  4 |     SORT ORDER BY STOPKEY          |                                | 45022 |  9848K|    10M|  6963   (1)| 00:01:24 |
|*  5 |      HASH JOIN RIGHT ANTI          |                                | 45022 |  9848K|       |  4773   (1)| 00:00:58 |
|   6 |       TABLE ACCESS BY INDEX ROWID  | DISTRIBUTE_MATCH_NUSER         |    91 |  4095 |       |    55   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN            | IDX_DISTRIBUTE_MATCH_NUSER_0   |    91 |       |       |     1   (0)| 00:00:01 |
|*  8 |       HASH JOIN                    |                                | 45113 |  7885K|  6216K|  4718   (1)| 00:00:57 |
|*  9 |        HASH JOIN SEMI              |                                | 45114 |  5683K|  4280K|  3071   (1)| 00:00:37 |
|  10 |         TABLE ACCESS BY INDEX ROWID| DISTRIBUTE_MATCH_RESULT        | 45114 |  3744K|       |  1626   (1)| 00:00:20 |
|* 11 |          INDEX RANGE SCAN          | IDX_DISTRIBUTE_MATCH_RESULT_12 | 45124 |       |       |   160   (0)| 00:00:02 |
|* 12 |         INDEX FAST FULL SCAN       | IDX_DISTRIBUTE_MATCH_ROLE      |   230K|  9912K|       |   625   (1)| 00:00:08 |
|* 13 |        TABLE ACCESS FULL           | ACT_RU_PROCINST_EXTEND         |   224K|    10M|       |   685   (1)| 00:00:09 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   4 - filter(ROWNUM<=10)
   5 - access("DMN"."TASK_ID"="DMR"."TASK_ID")
   7 - access("DMN"."CLERK_NO"='tj0010')
   8 - access("DMR"."PROC_INST_ID"="ARPE"."PROC_INST_ID")
   9 - access("DMR"."TASK_ID"="R"."TASK_ID")
  11 - access("DMR"."TASK_STATE"='1' AND "DMR"."DISTRIBUTE_STAGE"='RR' AND "DMR"."BRANCH_NO"='120000' AND
              "DMR"."IS_DELETED"='0')
  12 - filter("R"."ROLE_ID"='UWBPS' OR "R"."ROLE_ID"='ceshi' OR "R"."ROLE_ID"='tjtest')
  13 - filter("ARPE"."PROCESS_SUSPEND_STATE"='1')

打赏鼓励一下!
论坛徽章:
0
发表于 2017-1-12 13:12 | 显示全部楼层
MARK一下

使用道具 举报

回复
论坛徽章:
300
鲜花蛋
日期:2013-07-22 15:52:18马上有对象
日期:2014-08-19 18:14:38一汽
日期:2014-02-02 18:41:57红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
发表于 2017-1-12 14:53 | 显示全部楼层
不加 rownum < 10, >1 之类的条件,看看返回多少记录? 以此判断,排序耗时是否合理。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2017-1-12 15:16 | 显示全部楼层
ZALBB 发表于 2017-1-12 14:53
不加 rownum < 10, >1 之类的条件,看看返回多少记录? 以此判断,排序耗时是否合理。

去掉条件后返回结果数209982

  1. SELECT
  2.     count(1)
  3.     FROM
  4.       DISTRIBUTE_MATCH_RESULT DMR INNER JOIN ACT_RU_PROCINST_EXTEND ARPE ON DMR.PROC_INST_ID = ARPE.PROC_INST_ID
  5.     WHERE EXISTS(SELECT 1
  6.                  FROM DISTRIBUTE_MATCH_ROLE R
  7.                  WHERE DMR.TASK_ID = R.TASK_ID AND R.ROLE_ID IN ('ceshi','UWBPS','tjtest'))
  8.           AND DMR.TASK_STATE = '1'
  9.           AND NOT EXISTS(SELECT 1
  10.                      FROM DISTRIBUTE_MATCH_NUSER DMN
  11.                      WHERE DMN.TASK_ID = DMR.TASK_ID AND DMN.CLERK_NO = 'tj0010')
  12.           AND DMR.DISTRIBUTE_STAGE='RR'
  13.           AND DMR.BRANCH_NO = '120000'
  14.           AND DMR.IS_DELETED = '0'
  15.           AND ARPE.PROCESS_SUSPEND_STATE = '1'
  16.       ORDER BY TASK_PRIORITY DESC, WARN_FLAG DESC, TASK_CREATE_DATE ASC
复制代码

使用道具 举报

回复
论坛徽章:
300
鲜花蛋
日期:2013-07-22 15:52:18马上有对象
日期:2014-08-19 18:14:38一汽
日期:2014-02-02 18:41:57红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
发表于 2017-1-12 15:45 | 显示全部楼层
你只要10条数据,但系统返回20+万来排序,,,,我觉得有必要加强过滤条件,比如:只要某个时间点之后的数据,这样也许会快些,

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2017-1-12 16:51 | 显示全部楼层
ZALBB 发表于 2017-1-12 15:45
你只要10条数据,但系统返回20+万来排序,,,,我觉得有必要加强过滤条件,比如:只要某个时间点之后的数 ...

目前在进行压力测试阶段,所以数据方面可能不合理 ,如果基于A B两张表的两个字段来排序,同时待排序的数据量又不小比如5W左右 那有没有别的优化的思路

使用道具 举报

回复
论坛徽章:
300
鲜花蛋
日期:2013-07-22 15:52:18马上有对象
日期:2014-08-19 18:14:38一汽
日期:2014-02-02 18:41:57红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
发表于 2017-1-13 09:13 | 显示全部楼层
bluestar0817 发表于 2017-1-12 16:51
目前在进行压力测试阶段,所以数据方面可能不合理 ,如果基于A B两张表的两个字段来排序,同时待排序的数据 ...

据我了解,ORACLE 在排序这块,没有多大的优化余地,至多也就是多分配写内存,避免使用磁盘,但排序算法上,DBA无法做改动,控制要排序的数据量才是关键。

使用道具 举报

回复

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

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表