查看: 1937|回复: 9

[PL/SQL] <求助>请问大数据量去重以及索引相关问题

[复制链接]
论坛徽章:
0
发表于 2018-6-11 14:04 | 显示全部楼层 |阅读模式
先上SQL:    select *    from (select al.*,
                 row_number() over(partition by al.operation_log_uuid, al.operation_log_root, al.operation_type, al.operation_desc, al.operation_login_ip, al.operation_userid, al.model_main_display_value order by al.module_code desc) rn
            from (select *
                    from t1
                   where module_code = '7_module_code'
                     and entity_code = '7_module_9_entity_code') al) bb
   where bb.rn = 1


其中,t1表中大约有1300w的数据,已建module_code, entity_code列的索引。
结果集al中大约有130w数据,查询时间为1.3s左右。业务要求对al中的部分数据去重,认为partition by后面几个字段一致的结果为重复内容。
将结果集al放到临时表中,再查询大概在2s左右,但是直接执行上述sql时间简直突破天际。
执行计划如下所示
description                                               object owner object name              cost         cardinality  bytes                  
SELECT STATEMENT, GOAL = ALL_ROWS                                                         15149        14369        216713258
-VIEW                                                       MESAUDIT                                         15149        14369        216713258
  -WINDOW SORT PUSHED RANK                                                                         15149        14369        3692833
   -TABLE ACCESS BY INDEX ROWID               MESAUDIT        MES_AUDIT_LOG         14349        14369        3692833
    -INDEX RANGE SCAN                               MESAUDIT        MES_AUDIT_LOG_BAP 163                14369       


请问为何两种方式的执行效率差的这么多?

论坛徽章:
0
 楼主| 发表于 2018-6-11 14:05 | 显示全部楼层
请原谅我的渣排版,为什么发出来和编辑的排版差这么多。。。。

使用道具 举报

回复
论坛徽章:
310
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2018-6-11 14:26 | 显示全部楼层
你可以先强制执行 AL 表,得到结果后,再执行分析函数,可采用加提示 NO_MERGE 的方式,或者写成 WITH AL AS 的方式,要求优化器先计算里层,得到 AL 结果集。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-6-11 14:52 | 显示全部楼层
ZALBB 发表于 2018-6-11 14:26
你可以先强制执行 AL 表,得到结果后,再执行分析函数,可采用加提示 NO_MERGE 的方式,或者写成 WITH AL A ...

您好,使用with as的方式强制执行,执行计划与之前是一样的,从我这里看效率并没有提升。sql如下:
   
with al as(select *
                from MES_AUDIT_LOG
               where module_code = '7_module_code'
                 and entity_code = '7_module_9_entity_code')
        select
               al.*,
               ROW_NUMBER() OVER(PARTITION BY al.operation_log_uuid, al.operation_log_root, al.operation_type, al.operation_desc, al.operation_login_ip, al.operation_userid, al.model_main_display_value order by al.module_code desc) RN
        from   al

使用道具 举报

回复
论坛徽章:
310
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2018-6-12 08:58 | 显示全部楼层
你得列出,原先的执行计划是怎么样的(两语句),现在是怎么样的,供分析才好思考,

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-6-12 10:21 | 显示全部楼层
使用with as写法后的执行计划如下:
1        Plan hash value: 3302471350
2         
3        ---------------------------------------------------------------------------------------------------------------
4        | Id  | Operation                     | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
5        ---------------------------------------------------------------------------------------------------------------
6        |   0 | SELECT STATEMENT              |                       | 14369 |  3606K|       |  4359   (1)| 00:00:53 |
7        |   1 |  WINDOW SORT                  |                       | 14369 |  3606K|  4272K|  4359   (1)| 00:00:53 |
8        |   2 |   TABLE ACCESS BY INDEX ROWID | MES_AUDIT_LOG         | 14369 |  3606K|       |  3558   (1)| 00:00:43 |
9        |   3 |    BITMAP CONVERSION TO ROWIDS|                       |       |       |       |            |          |
10        |*  4 |     BITMAP INDEX RANGE SCAN   | MES_AUDIT_LOG_BAP_BIT |       |       |       |            |          |
11        ---------------------------------------------------------------------------------------------------------------
12         
13        Predicate Information (identified by operation id):
14        ---------------------------------------------------
15         
16           4 - access("MODULE_CODE"='7_module_code' AND "ENTITY_CODE"='7_module_9_entity_code')
17               filter("MODULE_CODE"='7_module_code' AND "ENTITY_CODE"='7_module_9_entity_code')


原sql写法执行计划如下:
1        Plan hash value: 3693184503
2         
3        ----------------------------------------------------------------------------------------------------------------
4        | Id  | Operation                      | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
5        ----------------------------------------------------------------------------------------------------------------
6        |   0 | SELECT STATEMENT               |                       | 14369 |   206M|       |  4359   (1)| 00:00:53 |
7        |*  1 |  VIEW                          |                       | 14369 |   206M|       |  4359   (1)| 00:00:53 |
8        |*  2 |   WINDOW SORT PUSHED RANK      |                       | 14369 |  3606K|  4272K|  4359   (1)| 00:00:53 |
9        |   3 |    TABLE ACCESS BY INDEX ROWID | MES_AUDIT_LOG         | 14369 |  3606K|       |  3558   (1)| 00:00:43 |
10        |   4 |     BITMAP CONVERSION TO ROWIDS|                       |       |       |       |            |          |
11        |*  5 |      BITMAP INDEX RANGE SCAN   | MES_AUDIT_LOG_BAP_BIT |       |       |       |            |          |
12        ----------------------------------------------------------------------------------------------------------------
13         
14        Predicate Information (identified by operation id):
15        ---------------------------------------------------
16         
17           1 - filter("BB"."RN"=1)
18           2 - filter(ROW_NUMBER() OVER ( PARTITION BY "MES_AUDIT_LOG"."OPERATION_LOG_UUID","MES_AUDIT_LOG"."OPE
19                      RATION_LOG_ROOT","MES_AUDIT_LOG"."OPERATION_TYPE","MES_AUDIT_LOG"."OPERATION_DESC","MES_AUDIT_LOG"."OPER
20                      ATION_LOGIN_IP","MES_AUDIT_LOG"."OPERATION_USERID","MES_AUDIT_LOG"."MODEL_MAIN_DISPLAY_VALUE" ORDER BY

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
发表于 2018-6-13 17:46 来自手机 | 显示全部楼层
用手机不太容易看,没仔细看,但凭感觉,al子查询,这个数据量1.2s,除非比较特殊的情况,无论走FTS还是索引再回表,都不容易完成,所以,楼主可能在单独执行al子查询时与给出SQL语句中的子查询不同,走了索引,但根本没回表。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-6-14 10:35 | 显示全部楼层
sqysl 发表于 2018-6-13 17:46
用手机不太容易看,没仔细看,但凭感觉,al子查询,这个数据量1.2s,除非比较特殊的情况,无论走FTS还是索 ...

应该是在作为单独查询的时候没有回表,但是查询的条件都是一致的,应该都是走索引了的,排序项也是有索引的项。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-6-14 12:28 | 显示全部楼层
本帖最后由 不若艳阳 于 2018-6-14 12:41 编辑

已经在数据输入的时候,排除了重复项,修改了业务逻辑,不再需要去重了,谢谢各位。
在数据量较大的情况下,排序还是太耗时了,但又不得不排序。
执行以下SQL
explain plan for Select
*
  From (Select row_.*, rownum rownum_
          From (Select al.id,
                       al.OPERATION_LOG_UUID,
                       al.OPERATION_LOG_ROOT,
                       al.OPERATION_TYPE,
                       al.OPERATION_DESC,
                       al.OPERATION_LOGIN_IP,
                       al.OPERATION_TIMESTAMP,
                       al.OPERATION_USERID,
                       al.OPERATION_USERNAME,
                       al.MODEL_CODE,
                       al.MODEL_NAME,
                       al.MODULE_CODE,
                       al.MODULE_NAME,
                       al.ENTITY_CODE,
                       al.ENTITY_NAME,
                       al.BUSINESS_ID,
                       al.MODEL_MAIN_DISPLAY_VALUE,
                       al.IS_MAIN_MODEL
                  From MES_AUDIT_LOG al
                 Where 1 = 1
                   and al.module_code = '8_module_code'
                   and al.entity_code = '8_module_4_entity_code'
                 Order by id DESC) row_
         Where rownum <= 40)
Where rownum_ > 20

执行计划为
1        Plan hash value: 3128883808
2         
3        --------------------------------------------------------------------------------------------------------------------
4        | Id  | Operation                        | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
5        --------------------------------------------------------------------------------------------------------------------
6        |   0 | SELECT STATEMENT                 |                         |    40 |   589K|       |  2109   (1)| 00:00:26 |
7        |*  1 |  VIEW                            |                         |    40 |   589K|       |  2109   (1)| 00:00:26 |
8        |*  2 |   COUNT STOPKEY                  |                         |       |       |       |            |          |
9        |   3 |    VIEW                          |                         |  6916 |    99M|       |  2109   (1)| 00:00:26 |
10        |*  4 |     SORT ORDER BY STOPKEY        |                         |  6916 |  1735K|  2064K|  2109   (1)| 00:00:26 |
11        |   5 |      TABLE ACCESS BY INDEX ROWID | MES_AUDIT_LOG           |  6916 |  1735K|       |  1722   (1)| 00:00:21 |
12        |   6 |       BITMAP CONVERSION TO ROWIDS|                         |       |       |       |            |          |
13        |*  7 |        BITMAP INDEX SINGLE VALUE | LOG_MODULE_ENTITY_INDEX |       |       |       |            |          |
14        --------------------------------------------------------------------------------------------------------------------
15         
16        Predicate Information (identified by operation id):
17        ---------------------------------------------------
18         
19           1 - filter("ROWNUM_">20)
20           2 - filter(ROWNUM<=40)

从数据量上来说,一共是95w的数据量,用于排序的id是主键,带了唯一索引。
返回时间需要60s左右,感觉对95w的数据进行排序,不应该要这么久啊。

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
发表于 2018-6-15 00:02 来自手机 | 显示全部楼层
不若艳阳 发表于 2018-6-14 12:28
已经在数据输入的时候,排除了重复项,修改了业务逻辑,不再需要去重了,谢谢各位。
在数据量较大的情况下 ...

通过索引并回表获取了95w行数据,耗时60s,不算夸张。关键是获取了所有的95w行数据,这个很关键,这里索引可能用的有问题。

使用道具 举报

回复

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

本版积分规则 发表回复

第67期:Neo4j图数据库平台架构最佳实践
【微学堂】10月18日 20:00(周四)

当下,数据的规模和类型每时每刻都在呈几何级数的增长,仅能够管理大量的数据是不够的,关键是能从海量数据中发掘出有用的信息,特别是数据之间的关联,能高效存储和处理数据之间关联的新型数据库为图数据库。 本讲座将介绍Neo4j图数据库的基本概念、设计特点、架构和经典应用场景实战分享。

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