oracle中使用group by優(yōu)化distinct
更新時間：2015年11月20日 16:06:56 投稿：mrr
這篇文章主要介紹了oracle中使用group by優(yōu)化distinct的相關(guān)資料,需要的朋友可以參考下
今天mentor給了一個sql語句優(yōu)化的任務(wù)。（環(huán)境是sql developer）有一個語句執(zhí)行很慢，查詢出來的結(jié)果有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上面，它會導(dǎo)致對全表掃描，而且會導(dǎo)致排序，然后刪除重復(fù)的記錄，所以速度很慢，因此需要優(yōu)化distinct。查了不少資料，并逐一嘗試，最后發(fā)現(xiàn)了一個非?？捎^的優(yōu)化結(jié)果，用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
最后執(zhí)行時間只有15.1秒，快了60多倍，不得不說這優(yōu)化效果還是很可觀的。不過查了很多資料，仍然沒有發(fā)現(xiàn)合理地解釋：為什么distinct 和group by的效率會有這么大差別。查的很多資料，講的基本都是兩者相差不大，實(shí)現(xiàn)也差不多。有待解決。
DISTINCT和GROUP BY這兩者本質(zhì)上應(yīng)該沒有可比性，distinct 取出唯一列，group by 是分組，但有時候在優(yōu)化的時候，在沒有聚合函數(shù)的時候，他們查出來的結(jié)果也一樣。
相關(guān)文章
Oracle多行數(shù)據(jù)合并為一行數(shù)據(jù)并將列數(shù)據(jù)轉(zhuǎn)為字段名三種方式
怎么合并多行記錄的字符串,一直是oracle新手喜歡問的SQL問題之一,下面這篇文章主要給大家介紹了關(guān)于Oracle多行數(shù)據(jù)合并為一行數(shù)據(jù)并將列數(shù)據(jù)轉(zhuǎn)為字段名的三種方式,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-06-06
Json數(shù)據(jù)一般使用varchar2(400),varchar2(32676)或者BLOB來存儲Json數(shù)據(jù)，這篇文章主要介紹了Oracle?處理json數(shù)據(jù)的相關(guān)知識,需要的朋友可以參考下2022-11-11
Oracle基礎(chǔ):通過sqlplus執(zhí)行sql語句后的結(jié)果進(jìn)行判斷
今天小編就為大家分享一篇關(guān)于Oracle基礎(chǔ):通過sqlplus執(zhí)行sql語句后的結(jié)果進(jìn)行判斷，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2018-12-12
Oracle數(shù)據(jù)庫中的控制文件管理以及常用參數(shù)設(shè)置
這篇文章主要介紹了Oracle數(shù)據(jù)庫中的控制文件管理以及常用參數(shù)設(shè)置方法,包括控制文件不一致問題的解決方法以及隱藏參數(shù)等技巧,需要的朋友可以參考下2015-12-12
在Oracle數(shù)據(jù)庫中同時更新兩張表的簡單方法
這篇文章主要介紹了在Oracle數(shù)據(jù)庫中同時更新兩張表的簡單方法,同時介紹了一種差異性合并更新的方法,需要的朋友可以參考下2015-11-11
Linux系統(tǒng)（X64）安裝Oracle11g完整安裝圖文教程另附基本操作
因項(xiàng)目需求，需要在64位linux系統(tǒng)中安裝Oracle 11g，在網(wǎng)上查了很多內(nèi)容，結(jié)合自己的實(shí)際經(jīng)驗(yàn)，終于安裝成功，記錄下來，分享給有需要的同志們，不謝哈！^_^2014-08-08
Oracle數(shù)據(jù)庫中通用的函數(shù)實(shí)例詳解
OracleSQL提供了用于執(zhí)行特定操作的專用函數(shù),這些函數(shù)大大增強(qiáng)了SQL語言的功能,下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫中通用函數(shù)的相關(guān)資料,需要的朋友可以參考下2022-03-03
這篇文章主要介紹了Oracle修改字段長度及屬性,本文通過實(shí)例代碼給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友參考下吧2024-05-05
深刻理解Oracle數(shù)據(jù)庫的啟動和關(guān)閉
深刻理解Oracle數(shù)據(jù)庫的啟動和關(guān)閉...2007-03-03
文章主要介紹了如何查詢和解除Oracle數(shù)據(jù)庫中被鎖定的表,通過執(zhí)行特定的SQL語句,可以獲取被鎖定表的相關(guān)信息,并通過指定會話ID和序列號來解除鎖定,同時,文章提醒執(zhí)行此操作時需要謹(jǐn)慎,確保了解其影響2024-11-11