|
SELECT
BD_MNG_NO, nvl(trim(DBT_CUS_NO),'0000000000000') DBT_CUS_NO,
nvl(trim(ACT_NO),'00000000000000') ACT_NO, BRC_CD, BSO_CD, ATH_CLS_CD,
CLT_EMP_ID, DBC_CUS_CLS, BD_CLS, BD_SRT, nvl(BD_AUT,'1'), nvl(MON_CNT,'1'),
BOND_RANK, trim(ENV_RANK), ACT_ITM, OLD_BOND_RANK,
SUM(BF_MON_BAL), SUM(BD_AMT) , SUM(UWD_PRL), SUM(PRL), SUM(BD_BAL),
sum(UWD_PRL) as bank_amt,
nvl(sum(case when bd_cls ='1' and lgr_gn_dt <= '201111'||'25' then UWD_PRL
when bd_cls ='2' then UWD_PRL
else 0 end),0) as brc_amt,
nvl(sum(case when bd_cls ='1' and lgr_gn_dt <= '201111'||'25' then UWD_PRL
when bd_cls ='2' then UWD_PRL
else 0 end),0) as emp_amt
FROM (
SELECT /*+ index (b BMIXP_BONDGET07) */ B.BD_MNG_NO, B.DBT_CUS_NO, B.ACT_NO,
B.BRC_CD, B.BSO_CD, B.ATH_CLS_CD, nvl(B.CLT_EMP_ID,'002229') CLT_EMP_ID,
B.DBC_CUS_CLS, B.BD_CLS, B.BD_SRT, B.BD_AUT,
CASE WHEN (BD_CLS = '1' and BD_SRT = '1') and MON_CNT > 12 THEN 13
WHEN (BD_CLS = '2' AND BD_SRT IN ('1','2')) and mon_cnt > 36 THEN 37
ELSE NVL(MON_CNT,'1') END MON_CNT
, nvl(BOND_RANK,'99') as bond_rank, nvl(trim(b.ENV_RANK),'Z') as ENV_RANK,
'0' ACT_ITM, nvl(B.BF_MON_BAL,0) BF_MON_BAL,
nvl(B.BD_AMT,0) BD_AMT , nvl(B.UWD_PRL,0) UWD_PRL, nvl(B.PRL,0) PRL,
nvl(B.BD_BAL,0) BD_BAL, LGR_GN_DT,
NVL(OLD_BOND_RANK,NVL(BOND_RANK,'99')) OLD_BOND_RANK
FROM TEST_TABLE b
WHERE BD_LGR_ST = '002'
AND LGR_GN_DT <= '20111104'
AND BD_CLS IN ('1','2') AND BD_SRT IN ('1','2')
AND ATH_CLS_CD IS NOT NULL
AND ATH_CLS_CD IN ('00001','00002','00003','00004','00005', '10001', '10002', '10003', '10004', '10005', '20001', '30001', '90001')
)
GROUP BY BD_MNG_NO, DBT_CUS_NO, ACT_NO,
ATH_CLS_CD,BRC_CD, BSO_CD, CLT_EMP_ID, DBC_CUS_CLS, BD_CLS, BD_SRT, BD_AUT, MON_CNT, BOND_RANK, trim(ENV_RANK), ACT_ITM,OLD_BOND_RANK
;
---------------------------------------------------------------------------------------------------------------------------
-- TEST_TABLE은 약 1000만건의 데이터가 있고 BD_LGR_ST = '002' 조건으로 100만건으로 압축이 됩니다.
현재 쿼리의 조회건수는 70만건 정도 이며 INDEX는 BMIXP_BONDGET07=> ATH_CLS_CD + BD_LGR_ST + BD_CLS + BD_SRT 로 잡혀있습니다.
조회 속도가 거의 20분 정도 걸리는데요.. 5분아래로 다운시키고 싶은데.. 이것 저것 해봐도 안되네요.. 혹시 방법있으시면 고수님들의 조언 부탁드립니다
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=125 Card=1 Bytes=202)
1 0 PARTITION LIST (ALL)
2 1 SORT (GROUP BY) (Cost=125 Card=1 Bytes=202)
3 2 INLIST ITERATOR
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TEST_TABLE' (Cost=122 Card=1 Bytes=202)
5 4 INDEX (RANGE SCAN) OF 'BMIXP_BONDGET07' (NON-UNIQUE) (Cost=121 Card=1)
|
