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

2023-10-22 00:40
文章标签 oracle 优化 group exists distinct 一用

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

关于distinct 和group 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结构，key为col的值，最后计算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。

相关文章

Vue3 的 shallowRef 和 shallowReactive：优化性能

Vue3 的 shallowRef 和 shallowReactive：优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉，那么对 shallowRef 和 shallowReactive 是否了解呢？ 在编程和数据结构中，“shallow”（浅层）通常指对数据结构的最外层进行操作，而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素，而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层（Shallow） 定义
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
使用opencv优化图片（画面变清晰）

使用opencv优化图片（画面变清晰）

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化，使其看起来更清晰，同时保持尺寸不变，通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多，主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器：相机传
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

查看原文>>>全流程SWAP农业模型数据制备、敏感性分析及气候变化影响实践技术应用 SWAP模型是由荷兰瓦赫宁根大学开发的先进农作物模型，它综合考虑了土壤-水分-大气以及植被间的相互作用；是一种描述作物生长过程的一种机理性作物生长模型。它不但运用Richard方程，使其能够精确的模拟土壤中水分的运动，而且耦合了WOFOST作物模型使作物的生长描述更为科学。 本文让更多的科研人员和农业工作者
阅读更多...
从状态管理到性能优化：全面解析 Android Compose

从状态管理到性能优化：全面解析 Android Compose

文章目录 引言一、Android Compose基本概念1.1 什么是Android Compose?1.2 Compose的优势1.3 如何在项目中使用Compose 二、Compose中的状态管理2.1 状态管理的重要性2.2 Compose中的状态和数据流2.3 使用State和MutableState处理状态2.4 通过ViewModel进行状态管理 三、Compose中的列表和滚动
阅读更多...
matlab读取NC文件（含group）

matlab读取NC文件（含group）

matlab读取NC文件（含group）： NC文件数据结构： 代码： % 打开 NetCDF 文件filename = 'your_file.nc'; % 替换为你的文件名% 使用 netcdf.open 函数打开文件ncid = netcdf.open(filename, 'NC_NOWRITE');% 查看文件中的组% 假设我们想读取名为 "group1" 的组groupName
阅读更多...
Oracle type （自定义类型的使用）

Oracle type （自定义类型的使用）

oracle - type   type定义： oracle中自定义数据类型 oracle中有基本的数据类型，如number，varchar2，date，numeric，float....但有时候我们需要特殊的格式， 如将name定义为（firstname,lastname）的形式，我们想把这个作为一个表的一列看待，这时候就要我们自己定义一个数据类型 格式 ：create or repla
阅读更多...
ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

在win7 64位系统下安装oracle11g，在使用Database configuration Assistant创建数据库时，在创建到85%的时候报错，错误如下： 解决办法： 在listener.ora中增加对BlueAeri-PC或ip地址的侦听，具体步骤如下： 1.启动Net Manager，在“监听程序”--Listener下添加一个地址，主机名写计
阅读更多...
Oracle Start With关键字

Oracle Start With关键字

Oracle Start With关键字 前言 旨在记录一些Oracle使用中遇到的各种各样的问题. 同时希望能帮到和我遇到同样问题的人. Start With (树查询) 问题描述: 在数据库中, 有一种比较常见得 设计模式, 层级结构 设计模式, 具体到 Oracle table中, 字段特点如下: ID, DSC, PID; 三个字段, 分别表示 当前标识的 ID(主键), DSC 当
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2