oracle之优化一用group by或exists优化distinct

2023-10-22 00:40

本文主要是介绍oracle之优化一用group by或exists优化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_adjustmentsINNER 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_cdFROM cab_cab_valuations_workingWHERE 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_adjustmentsINNER 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_cdFROM cab_cab_valuations_workingWHERE 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多倍,不得不说这优化效果还是很可观的。不过查了很多资料,仍然没有发现合理地解释:为什么distinctgroup by的效率会有这么大差别。查的很多资料,讲的基本都是两者相差不大,实现也差不多。有待解决。


关于distinctgroup by的去重逻辑浅析

在数据库操作中,我们常常遇到需要将数据去重计数的工作。例如:

表A,列col

ACABCDAB

结果就是一共出现4个不同的字母A、B、C、D

即结果为4

大体上我们可以选择count(distinct col)的方法和group+count的方法。

分别为:

select count(distinct col) from A;select count(1) from (select 1 from A group by col) alias;

两中方法实现有什么不同呢?

其实上述两中方法分别是在运算和存储上的权衡。

distinct需要将col列中的全部内容都存储在一个内存中,可以理解为一个hash结构,keycol的值,最后计算hash结构中有多少个key即可得到结果。

很明显,需要将所有不同的值都存起来。内存消耗可能较大。

group by的方式是先将col排序。而数据库中的group一般使用sort的方法,即数据库会先对col进行排序。而排序的基本理论是,时间复杂为nlogn,空间为1.,然后只要单纯的计数就可以了。优点是空间复杂度小,缺点是要进行一次排序,执行时间会较长。

两中方法各有优劣,在使用的时候,我们需要根据实际情况进行取舍。

具体情况可参考如下法则

数据分布去重方式原因
离散groupdistinct空间占用较大,在时间复杂度允许的情况下,group 可以发挥空间复杂度优势
集中distinctdistinct空间占用较小,可以发挥时间复杂度优势

两个极端:

1.数据列的所有数据都一样,即去重计数的结果为1时,用distinct最佳

2.如果数据列唯一,没有相同数值,用group 最好

当然,在group by时,某些数据库产品会根据数据列的情况智能地选择是使用排序去重还是hash去重,例如postgresql。当然,我们可以根据实际情况对执行计划进行人工的干预,而这不是这里要讨论的话题了。


使用EXISTS替换DISTINCT

当查询中包含的表之间有一对多的关系时,避免在SELECT子句中使用DISTICT,可以使用EXISTS替换。

--查询emp表中目前所有员工都在哪些部门工作(包括部门编号和部门名称)
--使用DISTINCT(低效)
SELECT DISTINCT d.deptno, d.dname FROM dept d, emp e WHERE d.deptno = e.deptno;

在这里插入图片描述

--使用EXISTS(高效)select d.deptno, d.dname from dept d
where exists (select 1 from emp e where e.deptno = d.deptno);

在这里插入图片描述


使用exists+使用exists代替in+使用exists代替distinct

使用exists代替in

  1. exists只检查行的存在性,in 检查实际的值,所以exists的性能比in
    验证
select * from emp 
where deptno in(select  deptno from dept where   loc='NEW YORK');select * from emp e
where  exists(select 1 from dept d where d.deptno=e.deptno and loc='NEW YORK');

在这里插入图片描述

使用exists代替distinct

  1. exists只检查行的存在性,distinct用于禁止重复行的显示,而且distinct在禁止重复行的显示前需要排序检索的行,所以exists的性能比distinct

验证

select distinct e.deptno,d.dname  from emp e,dept d
where e.deptno=d.deptno;select  d.deptno,d.dname  from dept d
where exists(select 1 from emp e where e.deptno=d.deptno);

这里写图片描述

这篇关于oracle之优化一用group by或exists优化distinct的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/257977

相关文章

Oracle迁移PostgreSQL隐式类型转换配置指南

《Oracle迁移PostgreSQL隐式类型转换配置指南》Oracle迁移PostgreSQL时因类型差异易引发错误,需通过显式/隐式类型转换、转换关系管理及冲突处理解决,并配合验证测试确保数据一致... 目录一、问题背景二、解决方案1. 显式类型转换2. 隐式转换配置三、维护操作1. 转换关系管理2.

Oracle查询表结构建表语句索引等方式

《Oracle查询表结构建表语句索引等方式》使用USER_TAB_COLUMNS查询表结构可避免系统隐藏字段(如LISTUSER的CLOB与VARCHAR2同名字段),这些字段可能为dbms_lob.... 目录oracle查询表结构建表语句索引1.用“USER_TAB_COLUMNS”查询表结构2.用“a

MySQL中EXISTS与IN用法使用与对比分析

《MySQL中EXISTS与IN用法使用与对比分析》在MySQL中,EXISTS和IN都用于子查询中根据另一个查询的结果来过滤主查询的记录,本文将基于工作原理、效率和应用场景进行全面对比... 目录一、基本用法详解1. IN 运算符2. EXISTS 运算符二、EXISTS 与 IN 的选择策略三、性能对比

Oracle数据库定时备份脚本方式(Linux)

《Oracle数据库定时备份脚本方式(Linux)》文章介绍Oracle数据库自动备份方案,包含主机备份传输与备机解压导入流程,强调需提前全量删除原库数据避免报错,并需配置无密传输、定时任务及验证脚本... 目录说明主机脚本备机上自动导库脚本整个自动备份oracle数据库的过程(建议全程用root用户)总结

小白也能轻松上手! 路由器设置优化指南

《小白也能轻松上手!路由器设置优化指南》在日常生活中,我们常常会遇到WiFi网速慢的问题,这主要受到三个方面的影响,首要原因是WiFi产品的配置优化不合理,其次是硬件性能的不足,以及宽带线路本身的质... 在数字化时代,网络已成为生活必需品,追剧、游戏、办公、学习都离不开稳定高速的网络。但很多人面对新路由器

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页(deeppagination)却成为了性能优化的一个挑战,在本文中,我们将... 目录引言:深分页,真的只是“翻页慢”那么简单吗?一、背景介绍二、深分页的性能问题三、业务场景分析四、

Linux进程CPU绑定优化与实践过程

《Linux进程CPU绑定优化与实践过程》Linux支持进程绑定至特定CPU核心,通过sched_setaffinity系统调用和taskset工具实现,优化缓存效率与上下文切换,提升多核计算性能,适... 目录1. 多核处理器及并行计算概念1.1 多核处理器架构概述1.2 并行计算的含义及重要性1.3 并

浅谈mysql的not exists走不走索引

《浅谈mysql的notexists走不走索引》在MySQL中,​NOTEXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引,下面就来介绍一下mysql的notexists走不走索... 在mysql中,​NOT EXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引。以下

MyBatisPlus如何优化千万级数据的CRUD

《MyBatisPlus如何优化千万级数据的CRUD》最近负责的一个项目,数据库表量级破千万,每次执行CRUD都像走钢丝,稍有不慎就引起数据库报警,本文就结合这个项目的实战经验,聊聊MyBatisPl... 目录背景一、MyBATis Plus 简介二、千万级数据的挑战三、优化 CRUD 的关键策略1. 查

Java Stream的distinct去重原理分析

《JavaStream的distinct去重原理分析》Javastream中的distinct方法用于去除流中的重复元素,它返回一个包含过滤后唯一元素的新流,该方法会根据元素的hashcode和eq... 目录一、distinct 的基础用法与核心特性二、distinct 的底层实现原理1. 顺序流中的去重