1. 일단 제 생각은 a.fix_act_date 필드에 인덱스를 만들고요~ (없으면 만들면 좋을듯 싶어요)
2. order by 는 지워버리고요
3. 힌트를 인덱스를 만들었던 fix_act_date로 줬으면 좋겠고요~ ( /*+ INDEX_ASC(table index) */ )
4. account.af_form b << 요넘이 풀스켄의 원인인듯 보이는데...where 조인절에 걸리지 않아서 좀 그렇네요~
<업무를 안다면 먼가 걸려야 하는게 맞을것 같습니다.>
5. a.fix_act_date BETWEEN TO_DATE('20090101','yyyymmdd') AND TO_DATE('20120630','yyyymmdd')
문장도 좀 바꿨으면 좋겠어요
account.af_fixmst a 대신에
(select * from account.af_fixmst where fix_act_date >= to_date('20090101'||'000000','yyyymmddhh24miss')
and fix_gbn_code = 'F') 로 바꿨으면 좋겠네요
제가 생각하는 쿼리를 종합하자면..
SELECT /*+ INDEX_ASC(만든인덱스명) */ a.fix_act_date "취득일자", a.fix_gbn_code||'-'||a.fix_mng_code||'-'||LTRIM(TO_CHAR(a.fix_seq_no,'00000')) "관리번호", a.fix_name "품명", NVL(b.fix_design_no,'') "품번", a.fix_act_amt "취득금액", d.chw_jeryobi "재료비", d.chw_drt_exp+d.chw_ind_exp "노무비", d.chw_chg_exp+d.chw_fix_exp "경비" FROM ( select * from account.af_fixmst where fix_gbn_code = 'F' and fix_act_date >= to_date('20090101'||'000000','yyyymmddhh24miss') and fix_act_date <= to_date('20120630'||'235959','yyyymmddhh24miss') ) a inner join account.aw_chlist c on a.fix_gbn_code = c.chl_gae AND a.fix_mst_ym = c.chl_yymm AND a.fix_mst_seq = c.chl_seqno inner join account.aw_chwon d on c.chl_gae = d.chw_gae AND c.chl_yymm = d.chw_job_yymm AND c. chl_seqno = d.chw_seqno left outer join account.af_form b on 1=1
SELECT a.fix_act_date "취득일자", a.fix_gbn_code||'-'||a.fix_mng_code||'-'||LTRIM(TO_CHAR(a.fix_seq_no,'00000')) "관리번호", a.fix_name "품명", NVL(b.fix_design_no,'') "품번", a.fix_act_amt "취득금액", d.chw_jeryobi "재료비", d.chw_drt_exp+d.chw_ind_exp "노무비", d.chw_chg_exp+d.chw_fix_exp "경비" FROM (select fix_act_date "취득일자", fix_gbn_code||'-'||fix_mng_code||'-'||LTRIM(TO_CHAR(fix_seq_no,'00000')) "관리번호", fix_name "품명", fix_act_amt "취득금액" from account.af_fixmst where fix_gbn_code = 'F' AND fix_act_date BETWEEN TO_DATE('20090101','yyyymmdd') AND TO_DATE('20120630','yyyymmdd') ORDER BY fix_act_date ) a, account.aw_chlist c, account.aw_chwon d WHERE a.fix_gbn_code = c.chl_gae AND a.fix_mst_ym = c.chl_yymm AND a.fix_mst_seq = c.chl_seqno AND c.chl_gae = d.chw_gae AND c.chl_yymm = d.chw_job_yymm AND c. chl_seqno = d.chw_seqno