待性能改善的一个SQL

select t.*, t.rowid from tb_tk_datasakusei_ctrl t;

alter table ATOMBB.TB_TK_JISSEKI_INFO_DETAIL add Ksai_Nengetsu char(6);
alter table ATOMBB.TB_TK_JISSEKI_INFO_DETAIL add Fee_TeiseiChu_Flg char(1) default ‘0’ not null;
select * from TB_TK_JISSEKI_INFO_DETAIL y where to_char(y.input_date_time,’YYYY/MM/DD’) = ‘2015/06/04’;
select * from TB_TK_JISSEKI_INFO_DETAIL y where y.tk_jisseki_detail_mngr_num = ‘00000000000014437910’

AND ttji.keisai_jisseki_mngr_num = ‘0000636149’
AND ttji.tk_jisseki_mngr_num = ‘000000012065168’
select * from tb_toukei_kijimen ttk where ttk.dsp_jyun = ‘9’

keisai_jisseki_mngr_num = ‘0000636149’
AND ttk.tk_jisseki_mngr_num = ‘000000012065168’
drop index ATOMBB.IX_TB_TK_JISSEKI_DETAIL_06_FK;
drop index ATOMBB.IX_TB_TK_JISSEKI_DETAIL_07_FK;

— Create/Recreate indexes
create index ATOMBB.IX_TB_TK_JISSEKI_DETAIL_06_FK on ATOMBB.TB_TK_JISSEKI_INFO_DETAIL (uriage_bu_ka_code)
tablespace USR_COMMON_INDEX8K
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index ATOMBB.IX_TB_TK_JISSEKI_DETAIL_07_FK on ATOMBB.TB_TK_JISSEKI_INFO_DETAIL (Gyosyu_MCode)
tablespace USR_COMMON_INDEX8K
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index ATOMBB.IX_TB_TK_JISSEKI_DETAIL_08_FK on ATOMBB.TB_TK_JISSEKI_INFO_DETAIL (Gyosyu_SCode)
tablespace USR_COMMON_INDEX8K
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index ATOMBB.IX_TB_TK_JISSEKI_DETAIL_09_FK on ATOMBB.TB_TK_JISSEKI_INFO_DETAIL (adv_nushi_code)
tablespace USR_COMMON_INDEX8K
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

create index ATOMBB.IX_TB_TK_JISSEKI_DETAIL_10_FK on ATOMBB.TB_TK_JISSEKI_INFO_DETAIL (ksai_nengetsu)
tablespace USR_COMMON_INDEX8K
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

select /*+index(ttjid IX_TB_TK_JISSEKI_DETAIL_06_FK)*/
　 ttbi.tk_np_sha_code AS tkNpShaNameRyaku,
‘2015/05’ AS uriageNengetsu,
ttjid.uriage_bu_ka_code AS uriageBuKaCode,
ttjid.Gyosyu_MCode AS gyosyuMcode,
ttjid.adv_nushi_code AS advNushiCode,
ttjid.Gyosyu_SCode AS gyosyuScode,
(select tsar.Shukei_Group_Code
from tb_shukei_adv_relation tsar
where ttbi.adv_baitai_bunrui_code = tsar.adv_baitai_bunrui_code
AND tsar.shukei_group_code IN
(‘001010’, ‘001020’, ‘011010’, ‘011020’, ‘011030’)
and rownum = 1) AS shukeiGroupCode,
ttbi.adv_baitai_bunrui_code AS advBaitaiBunruiCode,
ttbi.tk_np_sha_code AS tkNpShaCode,
ROUND(COALESCE(SUM(ttjid.kansan_dansu), 0) / 1000, 3) AS dansuSum
from TB_TK_BAITAI_INFO ttbi, TB_TK_JISSEKI_INFO_DETAIL ttjid
where ttbi.ksai_sha_code = SUBSTR(‘120’, 1, 1)
AND ttbi.keisai_jisseki_mngr_num = ttjid.keisai_jisseki_mngr_num
AND ttbi.finish_state_code = ‘1’
— AND ttjid.del_flg = ‘0’
AND ttjid.uriage_bu_ka_code = ‘120’

AND ttjid.Gyosyu_MCode IN (’20’, ’21’, ’22’, ’23’, ’24’, ’25’, ’26’)

AND ttjid.Gyosyu_SCode IN
(’10’, ’12’, ’13’, ’14’, ’16’, ’15’, ’19’, ’20’, ’18’, ’21’, ’23’, ’22’, ’25’, ’24’, ’27’, ’28’, ’26’, ’29’)

AND ttbi.tk_np_sha_code IN (‘N003’, ‘N004’)

AND ttbi.del_flg = ‘0’
AND to_char(ttbi.ksai_date, ‘YYYYMM’) =
to_char(add_months(to_date(‘2015/05’, ‘YYYY/MM’), -12), ‘YYYYMM’)
GROUP BY ttjid.uriage_bu_ka_code,
ttjid.Gyosyu_MCode,
ttjid.adv_nushi_code,
ttjid.Gyosyu_SCode,
ttbi.adv_baitai_bunrui_code,
ttbi.tk_np_sha_code
order by ttbi.tk_np_sha_code

———————————————————-

select *
from TB_TK_JISSEKI_INFO_DETAIL ttjid
where ttjid.del_flg = ‘0’
and ttjid.uriage_bu_ka_code = ‘140’
AND ttjid.ksai_nengetsu = ‘201405’

update TB_TK_JISSEKI_INFO_DETAIL ttjid
set ttjid.ksai_nengetsu = to_char(ttjid.input_date_time, ‘yyyyMM’)
where ttjid.del_flg = ‘0’
and ttjid.uriage_bu_ka_code = ‘140’

select ‘2014/06’ AS uriageNengetsu,
ttjid.uriage_bu_ka_code AS uriageBuKaCode,
ttjid.Gyosyu_MCode AS gyosyuMcode,
ttjid.adv_nushi_code AS advNushiCode,
ttjid.Gyosyu_SCode AS gyosyuScode,
(select tsar.Shukei_Group_Code
from tb_shukei_adv_relation tsar
where ttbi.adv_baitai_bunrui_code = tsar.adv_baitai_bunrui_code
AND tsar.shukei_group_code IN
(‘001010’, ‘001020’, ‘011010’, ‘011020’, ‘011030’)
and rownum = 1) AS shukeiGroupCode,
ttbi.adv_baitai_bunrui_code AS advBaitaiBunruiCode,
ttbi.tk_np_sha_code AS tkNpShaCode,
sum(ttjid.kansan_dansu) AS dansuSum
from (select ttbi.adv_baitai_bunrui_code,
ttbi.keisai_jisseki_mngr_num， ttbi.tk_np_sha_code
from TB_TK_BAITAI_INFO ttbi
where ttbi.ksai_sha_code = SUBSTR(‘140’, 1, 1)

AND ttbi.finish_state_code = ‘1’
AND ttbi.tk_np_sha_code IN (‘N003’, ‘N004’)
AND ttbi.del_flg = ‘0’
AND to_char(ttbi.ksai_date, ‘YYYYMM’) =
to_char(add_months(to_date(‘2015/05’, ‘YYYY/MM’), -12),
‘YYYYMM’)
) ttbi,
(select ttjid.keisai_jisseki_mngr_num,
ttjid.uriage_bu_ka_code,
ttjid.Gyosyu_MCode,
ttjid.adv_nushi_code,
ttjid.Gyosyu_SCode,
ttjid.kansan_dansu
from TB_TK_JISSEKI_INFO_DETAIL ttjid
where ttjid.del_flg = ‘0’
AND ttjid.uriage_bu_ka_code = ‘140’
AND ttjid.ksai_nengetsu = ‘201405’
) ttjid
where ttbi.keisai_jisseki_mngr_num = ttjid.keisai_jisseki_mngr_num
GROUP BY ttjid.uriage_bu_ka_code,
ttjid.Gyosyu_MCode,
ttjid.adv_nushi_code,
ttjid.Gyosyu_SCode,
ttbi.adv_baitai_bunrui_code,
ttbi.tk_np_sha_code
order by ttbi.tk_np_sha_code;