领购物优惠券9块9包邮
首页 > 数据库 > Oracle教程

oracle中使用group by优化distinct

   2015-11-26 0
核心提示：这篇文章主要介绍了oracle中使用group by优化distinct的相关资料,需要的朋友可以参考下

今天mentor给了一个sql语句优化的任务。（环境是sql developer）有一个语句执行很慢，查询出来的结果有17544条记录，但需970秒，速度很慢。语句是这样的：

SELECT DISTINCT  'AMEND_NEW', 
       reporttitle, 
       reportsubtitle, 
       cab_cab_transactions.branchcode, 
       cab_cab_transactions.prtfo_cd, 
       cab_cab_transactions.sstm_scrty_id, 
       cab_cab_transactions.sstm_trx_id, 
       cab_cab_transactions.trde_dttm, 
       cab_cab_transactions.efcte_dttm, 
       cab_cab_transactions.due_stlmnt_dt, 
       cab_cab_transactions.cncl_efcte_dttm, 
       cab_cab_transactions.trde_sstm_id, 
       cab_cab_transactions.trx_type_cd, 
       cab_cab_transactions.trx_type_dscrn, 
       cab_cab_transactions.trx_subtype_cd, 
       cab_cab_transactions.trde_stat_flg, 
       cab_cab_transactions.csh_cr_dr_indcr, 
       cab_cab_transactions.long_shrt_indcr, 
       cab_cab_transactions.lcl_crncy, 
       cab_cab_transactions.stlmt_crncy, 
       cab_cab_transactions.nomin_qty, 
       cab_cab_transactions.price, 
       cab_cab_transactions.lcl_cst, 
       cab_cab_transactions.prtfo_cst, 
       cab_cab_transactions.lcl_book_cst, 
       cab_cab_transactions.prtfo_book_cst, 
       cab_cab_transactions.lcl_sell_prcds, 
       cab_cab_transactions.prtfo_sell_prcds, 
       cab_cab_transactions.lcl_gnls, 
       cab_cab_transactions.prtfo_gnls, 
       cab_cab_transactions.lcl_acrd_intrt, 
       cab_cab_transactions.prtfo_acrd_intrt, 
       cab_cab_transactions.stlmt_crncy_stlmt_amt, 
       cab_cab_transactions.lcl_net_amt, 
       cab_cab_transactions.prtfo_net_amt, 
       cab_cab_transactions.fx_bght_amt, 
       cab_cab_transactions.fx_sold_amt, 
       cab_cab_transactions.prtfo_crncy_stlmt_amt, 
       cab_cab_transactions.prtfo_net_incme, 
       cab_cab_transactions.dvnd_crncy_net_incme, 
       cab_cab_transactions.dvnd_type_cd, 
       cab_cab_transactions.lcl_intrt_pd_rec, 
       cab_cab_transactions.prtfo_intrt_pd_rec, 
       cab_cab_transactions.lcl_dvdnd_pd_rec, 
       cab_cab_transactions.prtfo_dvdnd_pd_rec, 
       cab_cab_transactions.lcl_sundry_inc_pd_rec, 
       cab_cab_transactions.prtfo_sundry_inc_pd_rec, 
       cab_cab_transactions.bnk_csh_cptl_secid, 
       cab_cab_transactions.bnk_csh_inc_secid, 
       cab_cab_transactions.reportdate, 
       cab_cab_transactions.filename, 
        sysdate, 
       'e483448' 
   FROM cab_cfg_trx_type_mapping RIGHT JOIN(cab_cab_tran_adjustments 
      INNER JOIN cab_cab_transactions ON(cab_cab_transactions.branchcode = cab_cab_tran_adjustments.branchcode ) 
       AND(cab_cab_tran_adjustments.sstm_trx_id = cab_cab_transactions.sstm_trx_id)) ON(cab_cfg_trx_type_mapping.cab_trx_type_cd = cab_cab_transactions.trx_type_cd) 
       AND(nvl(cab_cfg_trx_type_mapping.cab_trx_subtype_cd,' ') = nvl(cab_cab_transactions.trx_subtype_cd,' ') 
       AND (cab_cfg_trx_type_mapping.branchcode=cab_cab_transactions.branchcode)) 
      WHERE cab_cab_transactions.prtfo_cd IN 
       (SELECT DISTINCT prtfo_cd 
        FROM cab_cab_valuations_working 
        WHERE created_by = 'e483448' 
          AND branchcode='ISA') 
       AND cab_cab_tran_adjustments.efcte_dttm > '2011-07-31' 
       AND cab_cab_tran_adjustments.efcte_dttm <= '2011-08-31' 
       AND eff_trde_stat_flg <> 'X' 
       AND cab_cab_transactions.branchcode = 'ISA' 
       AND cab_cab_tran_adjustments.branchcode = 'ISA' 
       AND(cab_cfg_trx_type_mapping.cab_reportgroup = 'CABValuation' OR cab_cfg_trx_type_mapping.cab_reportgroup IS NULL)

问题在distinct上面，它会导致对全表扫描，而且会导致排序，然后删除重复的记录，所以速度很慢，因此需要优化distinct。查了不少资料，并逐一尝试，最后发现了一个非常可观的优化结果，用group by。语句如下：

SELECT   'AMEND_NEW', 
       reporttitle, 
       reportsubtitle, 
       cab_cab_transactions.branchcode, 
       cab_cab_transactions.prtfo_cd, 
       cab_cab_transactions.sstm_scrty_id, 
       cab_cab_transactions.sstm_trx_id, 
       cab_cab_transactions.trde_dttm, 
       cab_cab_transactions.efcte_dttm, 
       cab_cab_transactions.due_stlmnt_dt, 
       cab_cab_transactions.cncl_efcte_dttm, 
       cab_cab_transactions.trde_sstm_id, 
       cab_cab_transactions.trx_type_cd, 
       cab_cab_transactions.trx_type_dscrn, 
       cab_cab_transactions.trx_subtype_cd, 
       cab_cab_transactions.trde_stat_flg, 
       cab_cab_transactions.csh_cr_dr_indcr, 
       cab_cab_transactions.long_shrt_indcr, 
       cab_cab_transactions.lcl_crncy, 
       cab_cab_transactions.stlmt_crncy, 
       cab_cab_transactions.nomin_qty, 
       cab_cab_transactions.price, 
       cab_cab_transactions.lcl_cst, 
       cab_cab_transactions.prtfo_cst, 
       cab_cab_transactions.lcl_book_cst, 
       cab_cab_transactions.prtfo_book_cst, 
       cab_cab_transactions.lcl_sell_prcds, 
       cab_cab_transactions.prtfo_sell_prcds, 
       cab_cab_transactions.lcl_gnls, 
       cab_cab_transactions.prtfo_gnls, 
       cab_cab_transactions.lcl_acrd_intrt, 
       cab_cab_transactions.prtfo_acrd_intrt, 
       cab_cab_transactions.stlmt_crncy_stlmt_amt, 
       cab_cab_transactions.lcl_net_amt, 
       cab_cab_transactions.prtfo_net_amt, 
       cab_cab_transactions.fx_bght_amt, 
       cab_cab_transactions.fx_sold_amt, 
       cab_cab_transactions.prtfo_crncy_stlmt_amt, 
       cab_cab_transactions.prtfo_net_incme, 
       cab_cab_transactions.dvnd_crncy_net_incme, 
       cab_cab_transactions.dvnd_type_cd, 
       cab_cab_transactions.lcl_intrt_pd_rec, 
       cab_cab_transactions.prtfo_intrt_pd_rec, 
       cab_cab_transactions.lcl_dvdnd_pd_rec, 
       cab_cab_transactions.prtfo_dvdnd_pd_rec, 
       cab_cab_transactions.lcl_sundry_inc_pd_rec, 
       cab_cab_transactions.prtfo_sundry_inc_pd_rec, 
       cab_cab_transactions.bnk_csh_cptl_secid, 
       cab_cab_transactions.bnk_csh_inc_secid, 
       cab_cab_transactions.reportdate, 
       cab_cab_transactions.filename, 
        sysdate, 
       'e483448' 
   FROM cab_cfg_trx_type_mapping RIGHT JOIN(cab_cab_tran_adjustments 
      INNER JOIN cab_cab_transactions ON(cab_cab_transactions.branchcode = cab_cab_tran_adjustments.branchcode ) 
       AND(cab_cab_tran_adjustments.sstm_trx_id = cab_cab_transactions.sstm_trx_id)) ON(cab_cfg_trx_type_mapping.cab_trx_type_cd = cab_cab_transactions.trx_type_cd) 
       AND(nvl(cab_cfg_trx_type_mapping.cab_trx_subtype_cd,' ') = nvl(cab_cab_transactions.trx_subtype_cd,' ') 
       AND (cab_cfg_trx_type_mapping.branchcode=cab_cab_transactions.branchcode)) 
      WHERE cab_cab_transactions.prtfo_cd IN 
       (SELECT DISTINCT prtfo_cd 
        FROM cab_cab_valuations_working 
        WHERE created_by = 'e483448' 
          AND branchcode='ISA') 
       AND cab_cab_tran_adjustments.efcte_dttm > '2011-07-31' 
       AND cab_cab_tran_adjustments.efcte_dttm <= '2011-08-31' 
       AND eff_trde_stat_flg <> 'X' 
       AND cab_cab_transactions.branchcode = 'ISA' 
       AND cab_cab_tran_adjustments.branchcode = 'ISA' 
       AND(cab_cfg_trx_type_mapping.cab_reportgroup = 'CABValuation' OR cab_cfg_trx_type_mapping.cab_reportgroup IS NULL) 
       GROUP BY  reporttitle, 
       reportsubtitle, 
       cab_cab_transactions.branchcode, 
       cab_cab_transactions.prtfo_cd, 
       cab_cab_transactions.sstm_scrty_id, 
       cab_cab_transactions.sstm_trx_id, 
       cab_cab_transactions.trde_dttm, 
       cab_cab_transactions.efcte_dttm, 
       cab_cab_transactions.due_stlmnt_dt, 
       cab_cab_transactions.cncl_efcte_dttm, 
       cab_cab_transactions.trde_sstm_id, 
       cab_cab_transactions.trx_type_cd, 
       cab_cab_transactions.trx_type_dscrn, 
       cab_cab_transactions.trx_subtype_cd, 
       cab_cab_transactions.trde_stat_flg, 
       cab_cab_transactions.csh_cr_dr_indcr, 
       cab_cab_transactions.long_shrt_indcr, 
       cab_cab_transactions.lcl_crncy, 
       cab_cab_transactions.stlmt_crncy, 
       cab_cab_transactions.nomin_qty, 
       cab_cab_transactions.price, 
       cab_cab_transactions.lcl_cst, 
       cab_cab_transactions.prtfo_cst, 
       cab_cab_transactions.lcl_book_cst, 
       cab_cab_transactions.prtfo_book_cst, 
       cab_cab_transactions.lcl_sell_prcds, 
       cab_cab_transactions.prtfo_sell_prcds, 
       cab_cab_transactions.lcl_gnls, 
       cab_cab_transactions.prtfo_gnls, 
       cab_cab_transactions.lcl_acrd_intrt, 
       cab_cab_transactions.prtfo_acrd_intrt, 
       cab_cab_transactions.stlmt_crncy_stlmt_amt, 
       cab_cab_transactions.lcl_net_amt, 
       cab_cab_transactions.prtfo_net_amt, 
       cab_cab_transactions.fx_bght_amt, 
       cab_cab_transactions.fx_sold_amt, 
       cab_cab_transactions.prtfo_crncy_stlmt_amt, 
       cab_cab_transactions.prtfo_net_incme, 
       cab_cab_transactions.dvnd_crncy_net_incme, 
       cab_cab_transactions.dvnd_type_cd, 
       cab_cab_transactions.lcl_intrt_pd_rec, 
       cab_cab_transactions.prtfo_intrt_pd_rec, 
       cab_cab_transactions.lcl_dvdnd_pd_rec, 
       cab_cab_transactions.prtfo_dvdnd_pd_rec, 
       cab_cab_transactions.lcl_sundry_inc_pd_rec, 
       cab_cab_transactions.prtfo_sundry_inc_pd_rec, 
       cab_cab_transactions.bnk_csh_cptl_secid, 
       cab_cab_transactions.bnk_csh_inc_secid, 
       cab_cab_transactions.reportdate, 
       cab_cab_transactions.filename

最后执行时间只有15.1秒，快了60多倍，不得不说这优化效果还是很可观的。不过查了很多资料，仍然没有发现合理地解释：为什么distinct 和group by的效率会有这么大差别。查的很多资料，讲的基本都是两者相差不大，实现也差不多。有待解决。

DISTINCT和GROUP BY这两者本质上应该没有可比性，distinct 取出唯一列，group by 是分组，但有时候在优化的时候，在没有聚合函数的时候，他们查出来的结果也一样。

 
标签： oracle distinct group by 优化
反对 0举报 0 评论 0
 

免责声明：本文仅代表作者个人观点，与乐学笔记（本网）无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
    本网站有部分内容均转载自其它媒体，转载目的在于传递更多信息，并不代表本网赞同其观点和对其真实性负责，若因作品内容、知识产权、版权和其他问题，请及时提供相关证明等材料并与我们留言联系，本网站将在规定时间内给予删除等相关处理.

  • 去重复的sql(Oracle) 去重复的英文
    1.利用group by 去重复2.可以利用下面的sql去重复，如下　　1) select id,name,sex from (select a.*,row_number() over(partition by a.id,a.set order by name) su from test a ) where su=1　　2)select id,name,sex from (select a.*,row_number() over(p
    02-10
  • Oracle SQL七次提速技巧
    以下SQL执行时间按序号递减。1,动态SQL，没有绑定变量，每次执行都做硬解析操作，占用较大的共享池空间，若共享池空间不足，会导致其他SQL语句的解析信息被挤出共享池。create or replace procedure proc1as beginfor i in 1..100000 loop    execute imme
    02-10
  • Oracle\SQL Server等及其他基本语句写法
    Oracle\SQL Server等及其他基本语句写法
    Oracle\SQL  Server等及其他基本语句写法目录一．Excel相关 11.Excel中写脚本范例： 12.提取字节 23. 提取单元格内字符 24.VLOOKUP函数： 2二．SQL语句汇总 21.建表： 22.增 33.删 44.查 65.改 236.Alter的应用 24三．数据库备份与恢复脚本 261. Oracle： 2
    02-10
  • SQL ORACLE case when函数用法
    case when 用法(1)简单case函数：格式：　　case 列名 　　when 条件值1 then 选项1　　when 条件值1 then 选项2......　　else 默认值 end例如：　　select 　　case job_level　　when '1' then '1111'　　when '2' then '2222'　　　when '3' then '3333
    02-10
  • Oracle迁移到MySQL性能下降的注意点 oracle数据
    背景：最近有较多的客户系统由原来由Oracle改造到MySQL后出现了性能问题CPU 100%，或是后台的CRM系统复杂SQL在业务高峰的时候出现堆积导致业务故障。在我的记忆里面淘宝最初从Oracle迁移到MySQL期间也遇到了很多SQL的性能问题，记忆最为深刻的子查询，当初的
    02-10
  • ORACLE中通过SQL语句(alter table)来增加、删除
    1.添加字段：alter table  表名  add (字段  字段类型)  [ default  '输入默认值']  [null/not null]  ;2.添加备注：comment on column  库名.表名.字段名 is  '输入的备注';  如： 我要在ers_data库中  test表 document_type字段添加备注  comm
    02-10
  • MySQL与Oracle 差异比较之六触发器
    触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT  before insert or update or delete on ES_FAC_UNIT  for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u
    02-10
  • Oracle的HINT可以强制指定SQL的执行计划，比如
    在Oracle中查看所有的表: select * from tab/dba_tables/dba_objects/cat; 看用户建立的表 :  select table_name from user_tables;  //当前用户的表 select table_name from all_tables;  //所有用户的表 select table_name from dba_tables;  //包
    02-10
  • Oracle sql 子字符串长度判断
    Oracle sql 子字符串长度判断 select t.* from d_table t WHEREsubstr(t.col,1,1)='8' and instr(t.col,'/')0 and length(substr(t.col,1,instr(t.col,'/')))5; 字符串的前两位都是数字：select * from d_table t WHERE regexp_like(substr(t.col,1,2), '^[
    02-10
  • Oracle、MySql、Sql Server比对
    MySql:廉价(部分免费)：当前，MySQL採用双重授权(DualLicensed)，他们是GPL和MySQLAB制定的商业许可协议。假设你在一个遵循GPL的***（开源）项目中使用MySQL，那么你能够遵循GPL协议免费使用MySQL。否则，你须要购买MySQLAB制定的那个商业许可协议。Windows $
    02-10
更多>MySQL教程
MySQL中表子查询与关联子查询的基础学习教程 CentOS系统下编译安装MySQL以及设置相关yum源的教程
更多>Oracle教程
Oracle数据库中表空间的基本管理操作小结 在Linux系统上同时监控多个Oracle数据库表空间的方法
更多>SQL Server
SQL SERVER修改函数名容易引发的问题分析 SQLSERVER分页查询关于使用Top方式和row_number()解析函数的不同
更多>MongoDB
教大家8天学通MongoDB——第一天 基础入门篇 MongoDB运行状态监控、性能分析工具mongostat详解
更多>PostgreSQL
PostgreSQL角色与用户管理介绍 Windows下PostgreSQL安装图解
点击排行
网站首页  |  关于我们  |  联系方式  |  使用协议  |  版权隐私  |  网站地图  |  网站留言  |  违规举报