oracle中使用group by优化distinct

2024-10-30 04:00:33

今天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 是分组，但有时候在优化的时候，在没有聚合函数的时候，他们查出来的结果也一样。

分析MySQL中优化distinct的技巧

有这样的一个需求:select count(distinct nick) from user_access_xx_xx; 这条sql用于统计用户访问的uv,由于单表的数据量在10G以上,即使在user_access_xx_xx上加上nick的索引, 通过查看执行计划,也为全索引扫描,sql在执行的时候,会对整个服务器带来抖动: root@db 09:00:12>select count(distinct nick) from user_access; +--------+ | count(dis
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_
Oracle数据库中SQL语句的优化技巧

在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
MySQL中索引优化distinct语句及distinct的多字段操作

MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB;
Oracle中分组查询group by用法规则详解

Oracle中group by用法在select 语句中可以使用group by 子句将行划分成较小的组,一旦使用分组后select操作的对象变为各个分组后的数据,使用聚组函数返回的是每一个组的汇总信息. 使用having子句限制返回的结果集.group by 子句可以将查询结果分组,并返回行的汇总信息Oracle 按照group by 子句中指定的表达式的值分组查询结果. 在带有group by 子句的查询语句中,在select 列表中指定的列要么是group by 子句中指定的列,要么包
Oracle之SQL语句性能优化（34条优化方法）

好多同学对sql的优化好像是知道的甚少,最近总结了以下34条仅供参考. (1)选择最有效率的表名顺序(只在基于规则的优化器中有效): ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. (2) WH
Oracle中游标Cursor基本用法详解

查询 SELECT语句用于从数据库中查询数据,当在PL/SQL中使用SELECT语句时,要与INTO子句一起使用,查询的返回值被赋予INTO子句中的变量,变量的声明是在DELCARE中.SELECT INTO语法如下: SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ PL/SQL
oracle中110个常用函数介绍

1. ASCII 返回与指定的字符对应的十进制数; SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2. CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C --
oracle中sql 正则写法详解

目录在Oracle 10g及以下版本中,可以使用POSIX扩展来进行正则匹配 Oracle 11g及以上版本是否走索引 Oracle中使用正则表达式需先使用REGEXP_LIKE函数来匹配字符串.正则表达式的语法与一般正则表达式相似,但具体的写法也受不同版本的Oracle环境不同. 在Oracle 10g及以下版本中,可以使用POSIX扩展来进行正则匹配如: ^:匹配字符串的开始位置:$:匹配字符串的结束位置:.:匹配任何单个字符:[ ]:匹配指定集合内的任何字符,如[abc]匹配a.b.
深入浅析SQL中的group by 和 having 用法

一.sql中的group by 用法解析: Group By语句从英文的字面意义上理解就是"根据(by)一定的规则进行分组(Group)". 作用:通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理. 注意:group by 是先排序后分组! 举例说明:如果要用到group by 一般用到的就是"每"这个字, 例如现在有一个这样的需求:查询每个部门有多少人.就要用到分组的技术 select DepartmentID as '部门名称',

oracle中使用group by优化distinct

相关推荐

随机推荐