oracle中使用groupby优化distinct_oracle优化distinct

acl

147 浏览量更新于2023-03-16 评论收藏 47KB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源评论

资源推荐

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。语句如下：

本内容试读结束，登录后可阅读更多

下载后可阅读完整内容，剩余2页未读，立即下载

weixin_38640072

粉丝: 3
资源: 930

会员权益专享

oracle中使用group by优化distinct

评论0

会员权益专享

最新资源

oracle中使用group by优化distinct

评论0

group by的多种用法

Oracle基础之group by和聚合函数

处理group by 查询速度慢的问题.docx

oracle优化distinct,oracle 索引优化之distinct

oracle group by 和distinct

oracle 中 去重用group by 和distinct 哪个快

Oracle distinct group by

oracle distinct和groupby哪个效率高

oracle listagg 不支持 DISTINCT

oracle listagg 不支持distinct

oracle查询过滤重复行

oracle group_concat 去重

Oracle 去掉重复数据的方式以及区别

oracle 分组函数去重,Oracle如何实现多个字段去重

ORACLE 中的去重

oracle过滤重复数据

oracle 怎么去除列重复

oracle 过滤查询重复值

oracle sql语句过滤相同的结果

Oracle 的去重方法

会员权益专享

最新资源

oracle 中去重用group by 和distinct 哪个快