某医保系统的查询一年内一个中心所有医疗费登账记录的汇总情况执行3000多秒才能出结果，下面TOP SQL中执行时间最长的SQL语句它的功能是查询一年内一个中心所有医疗费登账记录的汇总情况，执行时间3286秒,则且这条SQL就是我们要找的SQL语句。
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 3,286 2,015 1 3,286 18.59 8aw9tm6w83usm JDBC Thin Client select t1.account_id, ... 622 316 17 36.60 3.52 ggrctzgtcg14s JDBC Thin Client select t.pay_type as pay_... 222 4 1 221.64 1.25 fvy1hnauapb8s JDBC Thin Client select (select center_name fro... 204 5 1 203.60 1.15 fcm5wrxny5b2u JDBC Thin Client select count(1) cs from bs_ho... 198 125 31 6.38 1.12 cfuhdxk446uzv JDBC Thin Client select * from ( select a.hosp... 177 20 1 177.00 1.00 42zd8jamnw4b4 PL/SQL Developer begin dbms_sqltune.execute_tu... 158 88 1 158.21 0.89 17t35rynaz40v JDBC Thin Client select u.center_name, x.hospit... 142 89 97 1.46 0.80 a7dkwg8uhrwkj JDBC Thin Client select * from ( select a.hosp... 135 48 1,417 0.10 0.76 aay99a855zv3u JDBC Thin Client SELECT distinct c.NAME, ... 116 73 303 0.38 0.66 31tf0tfn8nv6f JDBC Thin Client select a.pay_batch_no, a.pay_b...
其SQL如下所示:
select t1.account_id,
t1.hospital_id,
(select hosp_level
from bs_hospital a
where t1.hospital_id = a.hospital_id
and a.valid_flag = '1') as hosp_level,
t1.serial_no,
t1.icd,
(select t4.disease
from bs_disease t4
where t1.icd = t4.icd(+)
and t4.center_id = nvl(center.catalog_center, center.center_id)) as disease,
t1.biz_type,
t1.treatment_type,
t1.disease_type,
t1.reimburse_flag,
t1.corp_id,
t1.corp_type_code,
t2.corp_name,
t1.indi_id,
t1.pers_type,
t2.center_id,
t2.district_code,
decode(t2.center_id, t2.district_code, 0, 1) as out_type,
t1.biz_flag,
t1.biz_flag_old,
t2.name,
t2.idcard,
t2.sex,
t2.begin_date,
t2.end_date,
t1.fin_date,
t2.reg_date,
t1.disease_fee,
t1.violate_flag,
t1.patient_id,
t1.in_days,
decode(t1.allow_treat, '0', 0, 1) As allow_treat,
Decode(T1.Treatment_Type_Last,
'',
T1.Treatment_Type,
Decode(T1.Treatment_Type,
'120',
'120',
Decode(T1.Biz_Type,
'41',
'扣减已记帐金额',
'42',
'扣减已记帐金额',
T1.Treatment_Type_Last))) As Treatment_Type_Last,
t1.violate,
t1.pay_money,
t1.pay_fund,
t1.audit_money,
'0' as finish_flag,
t1.MONTH_DECL_SN,
t1.deal_flag,
t1.audit_deal_flag,
t1.declare_icd_guide,
t1.declare_audit_fee,
t1.declare_audit_fee_cl,
t1.first_icd_guide,
t1.first_audit_fee,
t1.first_audit_fee_cl,
t1.second_icd_guide,
t1.second_audit_fee,
t1.second_audit_fee_cl,
t1.PAY_otherfee,
t1.PAY_FUND003,
t1.PAY_FUND001,
t1.PAY_FUND201,
t1.PAY_FUND301,
t1.PAY_FUND999,
t1.PAY_FUND996,
t1.PAY_begin
from (select a.account_id,
a.hospital_id,
a.serial_no,
a.center_id,
a.icd,
a.biz_type,
a.treatment_type,
a.reimburse_flag,
a.corp_id,
a.corp_type_code,
a.indi_id,
a.pers_type,
a.biz_flag,
a.biz_flag_old,
a.fin_date,
a.violate_flag,
(select mbf.disease_type
from mt_biz_fin mbf
where mbf.valid_flag = '1'
and mbf.hospital_id = a.hospital_id
and mbf.serial_no = a.serial_no) disease_type,
(Select aa1.allow_treat
From pm_account_scene aa1
where aa1.hospital_id = a.hospital_id
and aa1.serial_no = a.serial_no
and aa1.valid_flag = '1') as allow_treat,
(select distinct bb1.patient_id
from mt_biz_fin bb1
where bb1.hospital_id = a.hospital_id
and bb1.serial_no = a.serial_no
and bb1.valid_flag = '1') as patient_id,
(select distinct bb1.in_days
from mt_biz_fin bb1
where bb1.hospital_id = a.hospital_id
and bb1.serial_no = a.serial_no
and bb1.valid_flag = '1') as in_days,
(select Max(audit_treat_value)
from pm_treat_audit aa
where aa.account_id = a.account_id
and aa.audit_staff_id = '2208'
and aa.audit_phase = '1'
and aa.valid_flag = '1') as treatment_type_last,
a.violate,
nvl((SELECT sum(pm_account_fund.pay_money)
FROM pm_account_fund
WHERE a.account_id = pm_account_fund.account_id
AND pm_account_fund.valid_flag = '1'),
0) as pay_money,
nvl((SELECT sum(pm_account_fund.pay_money)
FROM pm_account_fund
WHERE a.account_id = pm_account_fund.account_id
AND pm_account_fund.valid_flag = '1'
AND pm_account_fund.fund_id not in
('003', '999', '996', '997')),
0) as pay_fund,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '003'),
0) AS PAY_FUND003,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
0) AS PAY_FUND001,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '201'),
0) AS PAY_FUND201,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '301'),
0) AS PAY_FUND301,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '999'),
0) AS PAY_FUND999,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '996'),
0) AS PAY_FUND996,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID not in
('801', '001', '003', '201', '999')),
0) AS PAY_otherfee,
NVL((SELECT SUM(mprf.real_pay)
FROM mt_pay_record_fin mprf
WHERE A.hospital_id = mprf.hospital_id
AND A.serial_no = mprf.serial_no
AND mprf.policy_item_code = 'S01'),
0) AS PAY_begin,
(select nvl(sum(b.AUDIT_MONEY), 0)
from pm_fee_audit b
where a.account_id = b.account_id
and b.audit_staff_id = '2208'
and b.AUDIT_TYPE = '1'
and b.AUDIT_PHASE = '1'
and b.valid_flag = '1') as audit_money,
a.MONTH_DECL_SN,
a.deal_flag,
decode(nvl(a.disease_fee, 0),
0,
nvl((select nvl(max(ration), 0)
from pm_ration pr
where pr.center_id = a.center_id
and pr.hospital_id = a.hospital_id
and pr.treatment_type = a.treatment_type
and pr.year = to_char(a.fin_date, 'yyyy')
and (decode(a.pers_type,
'21',
'21',
'22',
'21',
'23',
'21',
'24',
'21',
'41',
'21',
'3',
'12',
'4',
'12',
'5',
'12',
'7',
'12',
'2') = pr.insr_no or
pr.insr_no = '0')
and rownum < 2
and pr.biz_flag = a.biz_flag),
0),
a.disease_fee) as disease_fee,
a.audit_flag as audit_deal_flag,
a.declare_icd_guide as declare_icd_guide,
a.declare_audit_fee as declare_audit_fee,
a.declare_audit_fee_cl as declare_audit_fee_cl,
a.first_icd_guide as first_icd_guide,
a.first_audit_fee as first_audit_fee,
a.first_audit_fee_cl as first_audit_fee_cl,
decode(a.biz_type,
'12',
decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
a.second_icd_guide) second_icd_guide,
a.second_audit_fee as second_audit_fee,
a.second_audit_fee_cl as second_audit_fee_cl
from pm_account_biz a
where 1 = 1
and ('0' = 'A' or a.reimburse_flag ='0')
and ('00' = '00' or a.biz_type ='00')
and a.center_id in ('430722')
and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
and a.valid_flag = '1'
and a.deal_flag in ('0', '1', '2')
and (nvl(a.staff1_finish_flag, '0') ='0' and
nvl(a.staff2_finish_flag, '0') = '0' and
nvl(a.staff3_finish_flag, '0') = '0')
group by a.account_id,
a.hospital_id,
a.serial_no,
a.center_id,
a.icd,
a.biz_type,
a.treatment_type,
a.reimburse_flag,
a.corp_id,
a.corp_type_code,
a.indi_id,
a.pers_type,
a.biz_flag,
a.biz_flag_old,
a.disease_fee,
a.fin_date,
a.violate_flag,
a.violate,
a.MONTH_DECL_SN,
a.deal_flag,
a.audit_flag,
a.declare_icd_guide,
a.declare_audit_fee,
a.declare_audit_fee_cl,
a.first_icd_guide,
a.first_audit_fee,
a.first_audit_fee_cl,
a.second_icd_guide,
a.second_audit_fee,
a.second_audit_fee_cl) t1,
mt_biz_fin t2,
bs_center center
where t1.serial_no = t2.serial_no
AND t1.hospital_id = t2.hospital_id
AND t2.valid_flag = '1'
and t1.center_id = center.center_id(+)
order by t1.hospital_id, t1.serial_no
上面SQL的执行计划如下：
---------------------------------------------------------------------------------------------------------------------------- | Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------------------- | 0 | 67 | SELECT STATEMENT | | | | 56871 (100)| | | 1 | 2 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 16 | 1 (0)| 00:00:01 | | 2 | 1 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | | 3 | 4 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | 1 (0)| 00:00:01 | | 4 | 3 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | 1 (0)| 00:00:01 | | 5 | 6 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | | 6 | 5 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 7 | 10 | SORT AGGREGATE | | 1 | 33 | | | | 8 | 9 | COUNT STOPKEY | | | | | | | 9 | 8 | TABLE ACCESS BY INDEX ROWID | PM_RATION | 1 | 33 | 1 (0)| 00:00:01 | | 10 | 7 | INDEX RANGE SCAN | IDX_PM_RATION_1 | 1 | | 1 (0)| 00:00:01 | | 11 | 13 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 | | 12 | 12 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | | 13 | 11 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 14 | 16 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 | | 15 | 15 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | | 16 | 14 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 17 | 18 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_SCENE | 1 | 28 | 1 (0)| 00:00:01 | | 18 | 17 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_SCENE_1 | 1 | | 1 (0)| 00:00:01 | | 19 | 21 | SORT AGGREGATE | | 1 | 37 | | | | 20 | 20 | TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 | | 21 | 19 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 | | 22 | 24 | SORT AGGREGATE | | 1 | 12 | | | | 23 | 23 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 12 | 1 (0)| 00:00:01 | | 24 | 22 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 25 | 27 | SORT AGGREGATE | | 1 | 16 | | | | 26 | 26 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 27 | 25 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 28 | 30 | SORT AGGREGATE | | 1 | 21 | | | | 29 | 29 | TABLE ACCESS BY INDEX ROWID | PM_FEE_AUDIT | 1 | 21 | 1 (0)| 00:00:01 | | 30 | 28 | INDEX RANGE SCAN | PK_PM_FEE_AUDIT | 1 | | 1 (0)| 00:00:01 | | 31 | 33 | SORT AGGREGATE | | 1 | 16 | | | | 32 | 32 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 33 | 31 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 34 | 36 | SORT AGGREGATE | | 1 | 16 | | | | 35 | 35 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 36 | 34 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 37 | 40 | SORT AGGREGATE | | 1 | 16 | | | | 38 | 39 | INLIST ITERATOR | | | | | | | 39 | 38 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 40 | 37 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 41 | 43 | SORT AGGREGATE | | 1 | 16 | | | | 42 | 42 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 43 | 41 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 44 | 46 | SORT AGGREGATE | | 1 | 16 | | | | 45 | 45 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 46 | 44 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 47 | 49 | SORT AGGREGATE | | 1 | 16 | | | | 48 | 48 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 49 | 47 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 50 | 52 | SORT AGGREGATE | | 1 | 16 | | | | 51 | 51 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 52 | 50 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 53 | 55 | SORT AGGREGATE | | 1 | 39 | | | | 54 | 54 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 39 | 1 (0)| 00:00:01 | | 55 | 53 | INDEX RANGE SCAN | IDX_MT_PAY_RECORD_FIN_2 | 1 | | 1 (0)| 00:00:01 | | 56 | 66 | FILTER | | | | | | | 57 | 65 | SORT GROUP BY | | 1 | 309 | 56871 (1)| 00:11:23 | | 58 | 64 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 119 | 1 (0)| 00:00:01 | | 59 | 63 | NESTED LOOPS | | 1 | 309 | 56869 (1)| 00:11:23 | | 60 | 61 | MERGE JOIN OUTER | | 1 | 190 | 56868 (1)| 00:11:23 | | 61 | 57 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_BIZ | 1 | 164 | 56867 (1)| 00:11:23 | | 62 | 56 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_SERIAL_DATE | 285K| | 209 (1)| 00:00:03 | | 63 | 60 | BUFFER SORT | | 1 | 26 | 1 (0)| 00:00:01 | | 64 | 59 | TABLE ACCESS BY INDEX ROWID| BS_CENTER | 1 | 26 | 1 (0)| 00:00:01 | | 65 | 58 | INDEX UNIQUE SCAN | PK_BS_CENTER | 1 | | 1 (0)| 00:00:01 | | 66 | 62 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------------
从其执行计划中可以看到最消耗成本的操作就是对IDX_PM_ACCOUNT_BIZ_SERIAL_DATE索引执行索引范围扫描返回285K条记录再回表查询记录的成本是56867，而整个SQL语句的成本是56871。再与BS_CENTER表执行排序合并连接，再与MT_BIZ_FIN执行嵌套循环连接，再执行group by操作。因为表BS_CENTER表只有10几条记录，且表中的center_id是主键，表PM_ACCOUNT_BIZ中center_id与其它列存在复合索引，理想的执行计划应该是先访问表BS_CENTER再与PM_ACCOUNT_BIZ表执行嵌套循环连接，再与MT_BIZ_FIN连接。而且上述SQL中的group by子句是可以去掉了，这里group by真正的作用只起到了去掉重复记录的作用，而PM_ACCOUNT_BIZ表的主键是account_id，在查询列中，所以可以去掉这个group by 子句。
上面的SQL语句简化如下形式：
select ...省略
from pm_account_biz a
where 1 = 1
and ('0' = 'A' or a.reimburse_flag ='0')
and ('00' = '00' or a.biz_type ='00')
and a.center_id in ('430722')
and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
and a.valid_flag = '1'
and a.deal_flag in ('0', '1', '2')
and (nvl(a.staff1_finish_flag, '0') ='0' and
nvl(a.staff2_finish_flag, '0') = '0' and
nvl(a.staff3_finish_flag, '0') = '0')
group by a.account_id,
a.hospital_id,
a.serial_no,
a.center_id,
a.icd,
a.biz_type,
a.treatment_type,
a.reimburse_flag,
a.corp_id,
a.corp_type_code,
a.indi_id,
a.pers_type,
a.biz_flag,
a.biz_flag_old,
a.disease_fee,
a.fin_date,
a.violate_flag,
a.violate,
a.MONTH_DECL_SN,
a.deal_flag,
a.audit_flag,
a.declare_icd_guide,
a.declare_audit_fee,
a.declare_audit_fee_cl,
a.first_icd_guide,
a.first_audit_fee,
a.first_audit_fee_cl,
a.second_icd_guide,
a.second_audit_fee,
a.second_audit_fee_cl) t1,
mt_biz_fin t2,
bs_center center
where t1.serial_no = t2.serial_no
AND t1.hospital_id = t2.hospital_id
AND t2.valid_flag = '1'
and t1.center_id = center.center_id(+)
order by t1.hospital_id, t1.serial_no
可以看到表mt_biz_fin，与bs_center可以合并到内嵌视图t1中直接与表pm_account_biz进行表连接,前面已经说过了可以去掉group by子句,进行改写后的SQL如下：
select t1.account_id,
t1.hospital_id,
(select hosp_level
from bs_hospital a
where t1.hospital_id = a.hospital_id
and a.valid_flag = '1') as hosp_level,
t1.serial_no,
t1.icd,
(select t4.disease
from bs_disease t4
where t1.icd = t4.icd(+)
and t4.center_id = nvl(t1.catalog_center, t1.center_id)) as disease,
t1.biz_type,
t1.treatment_type,
t1.disease_type,
t1.reimburse_flag,
t1.corp_id,
t1.corp_type_code,
t1.corp_name,
t1.indi_id,
t1.pers_type,
t1.center_id,
t1.district_code,
t1.out_type,
t1.biz_flag,
t1.biz_flag_old,
t1.name,
t1.idcard,
t1.sex,
t1.begin_date,
t1.end_date,
t1.fin_date,
t1.reg_date,
t1.disease_fee,
t1.violate_flag,
t1.patient_id,
t1.in_days,
decode(t1.allow_treat, '0', 0, 1) As allow_treat,
Decode(T1.Treatment_Type_Last,
'',
T1.Treatment_Type,
Decode(T1.Treatment_Type,
'120',
'120',
Decode(T1.Biz_Type,
'41',
'扣减已记帐金额',
'42',
'扣减已记帐金额',
T1.Treatment_Type_Last))) As Treatment_Type_Last,
t1.violate,
t1.pay_money,
t1.pay_fund,
t1.audit_money,
'0' as finish_flag,
t1.MONTH_DECL_SN,
t1.deal_flag,
t1.audit_deal_flag,
t1.declare_icd_guide,
t1.declare_audit_fee,
t1.declare_audit_fee_cl,
t1.first_icd_guide,
t1.first_audit_fee,
t1.first_audit_fee_cl,
t1.second_icd_guide,
t1.second_audit_fee,
t1.second_audit_fee_cl,
t1.PAY_otherfee,
t1.PAY_FUND003,
t1.PAY_FUND001,
t1.PAY_FUND201,
t1.PAY_FUND301,
t1.PAY_FUND999,
t1.PAY_FUND996,
t1.PAY_begin
from (select a.account_id,
a.hospital_id,
a.serial_no,
a.center_id,
a.icd,
a.biz_type,
a.treatment_type,
a.reimburse_flag,
a.corp_id,
a.corp_type_code,
t2.corp_name,
a.indi_id,
a.pers_type,
center.catalog_center,
t2.district_code,
decode(t2.center_id, t2.district_code, 0, 1) as out_type,
a.biz_flag,
a.biz_flag_old,
t2.name,
t2.idcard,
t2.sex,
t2.begin_date,
t2.end_date,
a.fin_date,
t2.reg_date,
a.violate_flag,
(select mbf.disease_type
from mt_biz_fin mbf
where mbf.valid_flag = '1'
and mbf.hospital_id = a.hospital_id
and mbf.serial_no = a.serial_no) disease_type,
(Select aa1.allow_treat
From pm_account_scene aa1
where aa1.hospital_id = a.hospital_id
and aa1.serial_no = a.serial_no
and aa1.valid_flag = '1') as allow_treat,
(select distinct bb1.patient_id
from mt_biz_fin bb1
where bb1.hospital_id = a.hospital_id
and bb1.serial_no = a.serial_no
and bb1.valid_flag = '1') as patient_id,
(select distinct bb1.in_days
from mt_biz_fin bb1
where bb1.hospital_id = a.hospital_id
and bb1.serial_no = a.serial_no
and bb1.valid_flag = '1') as in_days,
(select Max(audit_treat_value)
from pm_treat_audit aa
where aa.account_id = a.account_id
and aa.audit_staff_id = '2208'
and aa.audit_phase = '1'
and aa.valid_flag = '1') as treatment_type_last,
a.violate,
nvl((SELECT sum(pm_account_fund.pay_money)
FROM pm_account_fund
WHERE a.account_id = pm_account_fund.account_id
AND pm_account_fund.valid_flag = '1'),
0) as pay_money,
nvl((SELECT sum(pm_account_fund.pay_money)
FROM pm_account_fund
WHERE a.account_id = pm_account_fund.account_id
AND pm_account_fund.valid_flag = '1'
AND pm_account_fund.fund_id not in
('003', '999', '996', '997')),
0) as pay_fund,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '003'),
0) AS PAY_FUND003,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
0) AS PAY_FUND001,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '201'),
0) AS PAY_FUND201,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '301'),
0) AS PAY_FUND301,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '999'),
0) AS PAY_FUND999,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '996'),
0) AS PAY_FUND996,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID not in
('801', '001', '003', '201', '999')),
0) AS PAY_otherfee,
NVL((SELECT SUM(mprf.real_pay)
FROM mt_pay_record_fin mprf
WHERE A.hospital_id = mprf.hospital_id
AND A.serial_no = mprf.serial_no
AND mprf.policy_item_code = 'S01'),
0) AS PAY_begin,
(select nvl(sum(b.AUDIT_MONEY), 0)
from pm_fee_audit b
where a.account_id = b.account_id
and b.audit_staff_id = '2208'
and b.AUDIT_TYPE = '1'
and b.AUDIT_PHASE = '1'
and b.valid_flag = '1') as audit_money,
a.MONTH_DECL_SN,
a.deal_flag,
decode(nvl(a.disease_fee, 0),
0,
nvl((select nvl(max(ration), 0)
from pm_ration pr
where pr.center_id = a.center_id
and pr.hospital_id = a.hospital_id
and pr.treatment_type = a.treatment_type
and pr.year = to_char(a.fin_date, 'yyyy')
and (decode(a.pers_type,
'21',
'21',
'22',
'21',
'23',
'21',
'24',
'21',
'41',
'21',
'3',
'12',
'4',
'12',
'5',
'12',
'7',
'12',
'2') = pr.insr_no or
pr.insr_no = '0')
and rownum < 2
and pr.biz_flag = a.biz_flag),
0),
a.disease_fee) as disease_fee,
a.audit_flag as audit_deal_flag,
a.declare_icd_guide as declare_icd_guide,
a.declare_audit_fee as declare_audit_fee,
a.declare_audit_fee_cl as declare_audit_fee_cl,
a.first_icd_guide as first_icd_guide,
a.first_audit_fee as first_audit_fee,
a.first_audit_fee_cl as first_audit_fee_cl,
decode(a.biz_type,
'12',
decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
a.second_icd_guide) second_icd_guide,
a.second_audit_fee as second_audit_fee,
a.second_audit_fee_cl as second_audit_fee_cl
from bs_center center,pm_account_biz a,mt_biz_fin t2
where 1 = 1
and ('0' = 'A' or a.reimburse_flag ='0')
and ('00' = '00' or a.biz_type ='00')
and a.center_id in ('430701')
and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
and a.valid_flag = '1'
and a.deal_flag in ('0', '1', '2')
and (nvl(a.staff1_finish_flag, '0') ='0' and
nvl(a.staff2_finish_flag, '0') = '0' and
nvl(a.staff3_finish_flag, '0') = '0')
and a.serial_no = t2.serial_no
AND a.hospital_id = t2.hospital_id
AND t2.valid_flag = '1'
and a.center_id = center.center_id(+)
and center.center_id in ('430701')
order by a.hospital_id, a.serial_no
)t1
来测试执行一次,但是执行了10分钟还是没有执行完，其执行计划如下:
------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3 | 855 | 24453 (1)| 00:04:54 | |* 1 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 16 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | 1 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | 1 (0)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 7 | SORT AGGREGATE | | 1 | 34 | | | |* 8 | COUNT STOPKEY | | | | | | |* 9 | TABLE ACCESS BY INDEX ROWID | PM_RATION | 1 | 34 | 1 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | IDX_PM_RATION_1 | 1 | | 1 (0)| 00:00:01 | | 11 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 | |* 12 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 14 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 | |* 15 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | |* 17 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_SCENE | 1 | 28 | 1 (0)| 00:00:01 | |* 18 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_SCENE_1 | 1 | | 1 (0)| 00:00:01 | | 19 | SORT AGGREGATE | | 1 | 37 | | | |* 20 | TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 | | 22 | SORT AGGREGATE | | 1 | 37 | | | |* 23 | TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 | |* 24 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 | | 25 | SORT AGGREGATE | | 1 | 12 | | | |* 26 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 12 | 1 (0)| 00:00:01 | |* 27 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND1 | 1 | | 1 (0)| 00:00:01 | | 28 | SORT AGGREGATE | | 1 | 16 | | | |* 29 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 30 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 31 | SORT AGGREGATE | | 1 | 21 | | | |* 32 | TABLE ACCESS BY INDEX ROWID | PM_FEE_AUDIT | 1 | 21 | 1 (0)| 00:00:01 | |* 33 | INDEX RANGE SCAN | PK_PM_FEE_AUDIT | 1 | | 1 (0)| 00:00:01 | | 34 | SORT AGGREGATE | | 1 | 16 | | | |* 35 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 36 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 37 | SORT AGGREGATE | | 1 | 16 | | | |* 38 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 39 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 40 | SORT AGGREGATE | | 1 | 16 | | | | 41 | INLIST ITERATOR | | | | | | |* 42 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 43 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 44 | SORT AGGREGATE | | 1 | 16 | | | |* 45 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 46 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 47 | SORT AGGREGATE | | 1 | 16 | | | |* 48 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 49 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 50 | SORT AGGREGATE | | 1 | 16 | | | |* 51 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 52 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 53 | SORT AGGREGATE | | 1 | 16 | | | |* 54 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 55 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 56 | SORT AGGREGATE | | 1 | 39 | | | | 57 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 39 | 1 (0)| 00:00:01 | |* 58 | INDEX RANGE SCAN | IDX_MT_PAY_RECORD_FIN_2 | 1 | | 1 (0)| 00:00:01 | | 59 | SORT ORDER BY | | 3 | 855 | 24453 (1)| 00:04:54 | |* 60 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 107 | 1 (0)| 00:00:01 | | 61 | NESTED LOOPS | | 3 | 855 | 24452 (1)| 00:04:54 | | 62 | NESTED LOOPS | | 3 | 534 | 24451 (1)| 00:04:54 | | 63 | TABLE ACCESS BY INDEX ROWID| BS_CENTER | 1 | 14 | 1 (0)| 00:00:01 | |* 64 | INDEX UNIQUE SCAN | PK_BS_CENTER | 1 | | 1 (0)| 00:00:01 | |* 65 | TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ | 3 | 492 | 24450 (1)| 00:04:54 | |* 66 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_SERIAL_DATE | 118K| | 29 (7)| 00:00:01 | |* 67 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------
其执行计划中现在是选访问表BS_CENTER后再与PM_ACCOUNT_BIZ执行嵌套循环连接，与我们设想的一样，这里慢的主要原因是因为要对表连接之后的记录执行order by a.hospital_id, a.serial_no操作。PM_ACCOUNT_BIZ表的查询条件中有and a.center_id in (‘430701’) and (a.fin_date between to_date(‘2014-01-01 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and
to_date(‘2014-12-31 23:59:59’, ‘yyyy-mm-dd hh24:mi:ss’))，而在CENTER_ID与FIN_DATE列上有存在复合索引。索引信息如下所示，在创建索引时，索引列的记录默认是按升序来存储的，而查询中要进行升序操作的列就是hospital_id,serial_no，而这两列已经存在复合索引IDX_PM_ACCOUNT_BIZ_SERIAL_NO中，而该索引中首列是CENTER_ID，我们只要把fin_date列加入这个复合索引中就可以让CBO在执行查询时使用该索引且能避免排序操作，而在IDX_PM_ACCOUNT_BIZ_SERIAL_DATE索引的有两列分别是CENTER_ID与FIN_DATE所以可以将这两个索引合并成一个新的索引（create index idx_pm_account_biz_center_id on pm_account_biz(center_id,hospital_id,serial_no,fin_date),并删除原先的这两个索引。
SQL> select a.index_name,a.column_name,a.column_position 2 from user_ind_columns a where a.table_name='PM_ACCOUNT_BIZ' and a.index_name='IDX_PM_ACCOUNT_BIZ_SERIAL_NO'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------------------------------------------------------------------- --------------- IDX_PM_ACCOUNT_BIZ_SERIAL_NO CENTER_ID 1 IDX_PM_ACCOUNT_BIZ_SERIAL_NO HOSPITAL_ID 2 IDX_PM_ACCOUNT_BIZ_SERIAL_NO SERIAL_NO 3 SQL> select a.index_name,a.column_name,a.column_position 2 from user_ind_columns a where a.table_name='PM_ACCOUNT_BIZ' and a.index_name='IDX_PM_ACCOUNT_BIZ_SERIAL_DATE'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------------------------------------------------------------------- --------------- IDX_PM_ACCOUNT_BIZ_SERIAL_DATE CENTER_ID 1 IDX_PM_ACCOUNT_BIZ_SERIAL_DATE FIN_DATE
我们在创建新索引后，执行修改后的SQL语句：
SQL> set timing on
SQL> set autotrace traceonly
SQL> select t1.account_id,
2 t1.hospital_id,
3 (select hosp_level
4 from bs_hospital a
5 where t1.hospital_id = a.hospital_id
6 and a.valid_flag = '1') as hosp_level,
7 t1.serial_no,
8 t1.icd,
9 (select t4.disease
10 from bs_disease t4
11 where t1.icd = t4.icd(+)
12 and t4.center_id = nvl(t1.catalog_center, t1.center_id)) as disease,
13 t1.biz_type,
14 t1.treatment_type,
15 t1.disease_type,
16 t1.reimburse_flag,
17 t1.corp_id,
18 t1.corp_type_code,
19 t1.corp_name,
20 t1.indi_id,
21 t1.pers_type,
22 t1.center_id,
23 t1.district_code,
24 t1.out_type,
25 t1.biz_flag,
26 t1.biz_flag_old,
27 t1.name,
28 t1.idcard,
29 t1.sex,
30 t1.begin_date,
31 t1.end_date,
32 t1.fin_date,
33 t1.reg_date,
34 t1.disease_fee,
35 t1.violate_flag,
36 t1.patient_id,
37 t1.in_days,
38 decode(t1.allow_treat, '0', 0, 1) As allow_treat,
39 Decode(T1.Treatment_Type_Last,
40 '',
41 T1.Treatment_Type,
42 Decode(T1.Treatment_Type,
43 '120',
44 '120',
45 Decode(T1.Biz_Type,
46 '41',
47 '扣减已记帐金额',
48 '42',
49 '扣减已记帐金额',
50 T1.Treatment_Type_Last))) As Treatment_Type_Last,
51 t1.violate,
52 t1.pay_money,
53 t1.pay_fund,
54 t1.audit_money,
55 '0' as finish_flag,
56 t1.MONTH_DECL_SN,
57 t1.deal_flag,
58 t1.audit_deal_flag,
59 t1.declare_icd_guide,
60 t1.declare_audit_fee,
61 t1.declare_audit_fee_cl,
62 t1.first_icd_guide,
63 t1.first_audit_fee,
64 t1.first_audit_fee_cl,
65 t1.second_icd_guide,
66 t1.second_audit_fee,
67 t1.second_audit_fee_cl,
68 t1.PAY_otherfee,
69 t1.PAY_FUND003,
70 t1.PAY_FUND001,
71 t1.PAY_FUND201,
72 t1.PAY_FUND301,
73 t1.PAY_FUND999,
74 t1.PAY_FUND996,
75 t1.PAY_begin
76 from (select a.account_id,
77 a.hospital_id,
78 a.serial_no,
79 a.center_id,
80 a.icd,
81 a.biz_type,
82 a.treatment_type,
83 a.reimburse_flag,
84 a.corp_id,
85 a.corp_type_code,
86 t2.corp_name,
87 a.indi_id,
88 a.pers_type,
89 center.catalog_center,
90 t2.district_code,
91 decode(t2.center_id, t2.district_code, 0, 1) as out_type,
92 a.biz_flag,
93 a.biz_flag_old,
94 t2.name,
95 t2.idcard,
96 t2.sex,
97 t2.begin_date,
98 t2.end_date,
99 a.fin_date,
100 t2.reg_date,
101 a.violate_flag,
102 (select mbf.disease_type
103 from mt_biz_fin mbf
104 where mbf.valid_flag = '1'
105 and mbf.hospital_id = a.hospital_id
106 and mbf.serial_no = a.serial_no) disease_type,
107 (Select aa1.allow_treat
108 From pm_account_scene aa1
109 where aa1.hospital_id = a.hospital_id
110 and aa1.serial_no = a.serial_no
111 and aa1.valid_flag = '1') as allow_treat,
112 (select distinct bb1.patient_id
113 from mt_biz_fin bb1
114 where bb1.hospital_id = a.hospital_id
115 and bb1.serial_no = a.serial_no
116 and bb1.valid_flag = '1') as patient_id,
117 (select distinct bb1.in_days
118 from mt_biz_fin bb1
119 where bb1.hospital_id = a.hospital_id
120 and bb1.serial_no = a.serial_no
121 and bb1.valid_flag = '1') as in_days,
122 (select Max(audit_treat_value)
123 from pm_treat_audit aa
124 where aa.account_id = a.account_id
125 and aa.audit_staff_id = '2208'
126 and aa.audit_phase = '1'
127 and aa.valid_flag = '1') as treatment_type_last,
128 a.violate,
129 nvl((SELECT sum(pm_account_fund.pay_money)
130 FROM pm_account_fund
131 WHERE a.account_id = pm_account_fund.account_id
132 AND pm_account_fund.valid_flag = '1'),
133 0) as pay_money,
134 nvl((SELECT sum(pm_account_fund.pay_money)
135 FROM pm_account_fund
136 WHERE a.account_id = pm_account_fund.account_id
137 AND pm_account_fund.valid_flag = '1'
138 AND pm_account_fund.fund_id not in
139 ('003', '999', '996', '997')),
140 0) as pay_fund,
141 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
142 FROM PM_ACCOUNT_FUND
143 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
144 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
145 AND PM_ACCOUNT_FUND.FUND_ID = '003'),
146 0) AS PAY_FUND003,
147 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
148 FROM PM_ACCOUNT_FUND
149 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
150 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
151 AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
152 0) AS PAY_FUND001,
153 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
154 FROM PM_ACCOUNT_FUND
155 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
156 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
157 AND PM_ACCOUNT_FUND.FUND_ID = '201'),
158 0) AS PAY_FUND201,
159 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
160 FROM PM_ACCOUNT_FUND
161 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
162 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
163 AND PM_ACCOUNT_FUND.FUND_ID = '301'),
164 0) AS PAY_FUND301,
165 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
166 FROM PM_ACCOUNT_FUND
167 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
168 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
169 AND PM_ACCOUNT_FUND.FUND_ID = '999'),
170 0) AS PAY_FUND999,
171 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
172 FROM PM_ACCOUNT_FUND
173 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
174 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
175 AND PM_ACCOUNT_FUND.FUND_ID = '996'),
176 0) AS PAY_FUND996,
177 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
178 FROM PM_ACCOUNT_FUND
179 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
180 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
181 AND PM_ACCOUNT_FUND.FUND_ID not in
182 ('801', '001', '003', '201', '999')),
183 0) AS PAY_otherfee,
184 NVL((SELECT SUM(mprf.real_pay)
185 FROM mt_pay_record_fin mprf
186 WHERE A.hospital_id = mprf.hospital_id
187 AND A.serial_no = mprf.serial_no
188 AND mprf.policy_item_code = 'S01'),
189 0) AS PAY_begin,
190 (select nvl(sum(b.AUDIT_MONEY), 0)
191 from pm_fee_audit b
192 where a.account_id = b.account_id
193 and b.audit_staff_id = '2208'
194 and b.AUDIT_TYPE = '1'
195 and b.AUDIT_PHASE = '1'
196 and b.valid_flag = '1') as audit_money,
197 a.MONTH_DECL_SN,
198 a.deal_flag,
199 decode(nvl(a.disease_fee, 0),
200 0,
201 nvl((select nvl(max(ration), 0)
202 from pm_ration pr
203 where pr.center_id = a.center_id
204 and pr.hospital_id = a.hospital_id
205 and pr.treatment_type = a.treatment_type
206 and pr.year = to_char(a.fin_date, 'yyyy')
207 and (decode(a.pers_type,
208 '21',
209 '21',
210 '22',
211 '21',
212 '23',
213 '21',
214 '24',
215 '21',
216 '41',
217 '21',
218 '3',
219 '12',
220 '4',
221 '12',
222 '5',
223 '12',
224 '7',
225 '12',
226 '2') = pr.insr_no or
227 pr.insr_no = '0')
228 and rownum < 2
229 and pr.biz_flag = a.biz_flag),
230 0),
231 a.disease_fee) as disease_fee,
232 a.audit_flag as audit_deal_flag,
233 a.declare_icd_guide as declare_icd_guide,
234 a.declare_audit_fee as declare_audit_fee,
235 a.declare_audit_fee_cl as declare_audit_fee_cl,
236 a.first_icd_guide as first_icd_guide,
237 a.first_audit_fee as first_audit_fee,
238 a.first_audit_fee_cl as first_audit_fee_cl,
239 decode(a.biz_type,
240 '12',
241 decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
242 a.second_icd_guide) second_icd_guide,
243 a.second_audit_fee as second_audit_fee,
244 a.second_audit_fee_cl as second_audit_fee_cl
245 from bs_center center,pm_account_biz a,mt_biz_fin t2
246 where 1 = 1
247 and ('0' = 'A' or a.reimburse_flag ='0')
248 and ('00' = '00' or a.biz_type ='00')
249 and a.center_id in ('430701')
250 and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
251 to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
252 and a.valid_flag = '1'
253 and a.deal_flag in ('0', '1', '2')
254 and (nvl(a.staff1_finish_flag, '0') ='0' and
255 nvl(a.staff2_finish_flag, '0') = '0' and
256 nvl(a.staff3_finish_flag, '0') = '0')
257 and a.serial_no = t2.serial_no
258 AND a.hospital_id = t2.hospital_id
259 AND t2.valid_flag = '1'
260 and a.center_id = center.center_id(+)
261 and center.center_id in ('430701')
262 order by a.hospital_id, a.serial_no
263 )t1
264 ;
189998 rows selected.
Elapsed: 00:01:37.15
Execution Plan
----------------------------------------------------------
Plan hash value: 1778792342
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 855 | 20157 (1)| 00:04:02 |
|* 1 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 34 | | |
|* 8 | COUNT STOPKEY | | | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | PM_RATION | 1 | 34 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_PM_RATION_1 | 1 | | 1 (0)| 00:00:01 |
| 11 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 |
| 14 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_SCENE | 1 | 28 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_SCENE_1 | 1 | | 1 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 37 | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 |
| 22 | SORT AGGREGATE | | 1 | 37 | | |
|* 23 | TABLE ACCESS BY INDEX ROWID| PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 |
| 25 | SORT AGGREGATE | | 1 | 12 | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 12 | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND1 | 1 | | 1 (0)| 00:00:01 |
| 28 | SORT AGGREGATE | | 1 | 16 | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 31 | SORT AGGREGATE | | 1 | 21 | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | PM_FEE_AUDIT | 1 | 21 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | PK_PM_FEE_AUDIT | 1 | | 1 (0)| 00:00:01 |
| 34 | SORT AGGREGATE | | 1 | 16 | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 37 | SORT AGGREGATE | | 1 | 16 | | |
|* 38 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 40 | SORT AGGREGATE | | 1 | 16 | | |
| 41 | INLIST ITERATOR | | | | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 44 | SORT AGGREGATE | | 1 | 16 | | |
|* 45 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 47 | SORT AGGREGATE | | 1 | 16 | | |
|* 48 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 50 | SORT AGGREGATE | | 1 | 16 | | |
|* 51 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 53 | SORT AGGREGATE | | 1 | 16 | | |
|* 54 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 56 | SORT AGGREGATE | | 1 | 39 | | |
| 57 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 39 | 1 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_MT_PAY_RECORD_FIN_2 | 1 | | 1 (0)| 00:00:01 |
|* 59 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 107 | 1 (0)| 00:00:01 |
| 60 | NESTED LOOPS | | 3 | 855 | 20157 (1)| 00:04:02 |
| 61 | NESTED LOOPS | | 3 | 534 | 20156 (1)| 00:04:02 |
| 62 | TABLE ACCESS BY INDEX ROWID| BS_CENTER | 1 | 14 | 1 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | PK_BS_CENTER | 1 | | 1 (0)| 00:00:01 |
|* 64 | TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ | 3 | 492 | 20155 (1)| 00:04:02 |
|* 65 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_CENTER_ID | 118K| | 178 (4)| 00:00:03 |
|* 66 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."VALID_FLAG"='1')
2 - access("A"."HOSPITAL_ID"=:B1)
4 - access("T4"."CENTER_ID"=NVL(:B1,:B2) AND "T4"."ICD"=:B3)
5 - filter("MBF"."VALID_FLAG"='1')
6 - access("MBF"."HOSPITAL_ID"=:B1 AND "MBF"."SERIAL_NO"=:B2)
8 - filter(ROWNUM<2)
9 - filter(("PR"."INSR_NO"=TO_NUMBER(DECODE(:B1,'21','21','22','21','23','21','24','21','41','21','3
','12','4','12','5','12','7','12','2')) OR "PR"."INSR_NO"=0) AND TO_NUMBER("PR"."BIZ_FLAG")=:B2)
10 - access("PR"."YEAR"=TO_NUMBER(TO_CHAR(:B1,'yyyy')) AND "PR"."HOSPITAL_ID"=:B2 AND
"PR"."TREATMENT_TYPE"=:B3 AND "PR"."CENTER_ID"=:B4)
filter("PR"."TREATMENT_TYPE"=:B1 AND "PR"."CENTER_ID"=:B2)
12 - filter("BB1"."VALID_FLAG"='1')
13 - access("BB1"."HOSPITAL_ID"=:B1 AND "BB1"."SERIAL_NO"=:B2)
15 - filter("BB1"."VALID_FLAG"='1')
16 - access("BB1"."HOSPITAL_ID"=:B1 AND "BB1"."SERIAL_NO"=:B2)
17 - filter("AA1"."VALID_FLAG"='1')
18 - access("AA1"."HOSPITAL_ID"=:B1 AND "AA1"."SERIAL_NO"=:B2)
20 - filter("AA"."VALID_FLAG"='1')
21 - access("AA"."ACCOUNT_ID"=:B1 AND "AA"."AUDIT_PHASE"='1' AND "AA"."AUDIT_STAFF_ID"='2208')
filter("AA"."AUDIT_STAFF_ID"='2208' AND "AA"."AUDIT_PHASE"='1')
23 - filter("AA"."VALID_FLAG"='1')
24 - access("AA"."ACCOUNT_ID"=:B1 AND "AA"."AUDIT_PHASE"='1' AND "AA"."AUDIT_STAFF_ID"='2208')
filter("AA"."AUDIT_STAFF_ID"='2208' AND "AA"."AUDIT_PHASE"='1')
26 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
27 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
29 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
30 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
filter("PM_ACCOUNT_FUND"."FUND_ID"<>'003' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'999' AND
"PM_ACCOUNT_FUND"."FUND_ID"<>'996' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'997')
32 - filter("B"."VALID_FLAG"='1')
33 - access("B"."ACCOUNT_ID"=:B1 AND "B"."AUDIT_TYPE"='1' AND "B"."AUDIT_PHASE"='1' AND
"B"."AUDIT_STAFF_ID"='2208')
filter("B"."AUDIT_STAFF_ID"='2208')
35 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
36 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
filter("PM_ACCOUNT_FUND"."FUND_ID"<>'003' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'999' AND
"PM_ACCOUNT_FUND"."FUND_ID"<>'001' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'801' AND
"PM_ACCOUNT_FUND"."FUND_ID"<>'201')
38 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
39 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='003')
42 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
43 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND ("PM_ACCOUNT_FUND"."FUND_ID"='001' OR
"PM_ACCOUNT_FUND"."FUND_ID"='801'))
45 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
46 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='201')
48 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
49 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='301')
51 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
52 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='999')
54 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
55 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='996')
58 - access("MPRF"."HOSPITAL_ID"=:B1 AND "MPRF"."SERIAL_NO"=:B2 AND "MPRF"."POLICY_ITEM_CODE"='S01')
59 - filter("T2"."VALID_FLAG"='1')
63 - access("CENTER"."CENTER_ID"='430701')
64 - filter(NVL("A"."STAFF2_FINISH_FLAG",'0')='0' AND NVL("A"."STAFF1_FINISH_FLAG",'0')='0' AND
NVL("A"."STAFF3_FINISH_FLAG",'0')='0' AND "A"."REIMBURSE_FLAG"='0' AND "A"."VALID_FLAG"='1' AND
("A"."DEAL_FLAG"='0' OR "A"."DEAL_FLAG"='1' OR "A"."DEAL_FLAG"='2'))
65 - access("A"."CENTER_ID"="CENTER"."CENTER_ID" AND "A"."FIN_DATE">=TO_DATE(' 2014-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "A"."FIN_DATE"< =TO_DATE(' 2014-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss')) filter("A"."FIN_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A"."CENTER_ID"='430701' AND "A"."FIN_DATE"< =TO_DATE(' 2014-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
66 - access("A"."HOSPITAL_ID"="T2"."HOSPITAL_ID" AND "A"."SERIAL_NO"="T2"."SERIAL_NO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2319157 consistent gets
8301 physical reads
576 redo size
2809640 bytes sent via SQL*Net to client
24709 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
189998 rows processed
从上面的实际执行情况可以看到返回了接近19万条记录花费的时间是1分37秒，也就是97秒与3286秒，已经是几十倍的提升了。
小结:这里优化的方法是通过修改SQL语句，改变表连接的方法，去掉不必要的分组(group by)操作，创建来合理的复合索引来避免排序(order by）来达到性能的提升。
初来乍到，踩一下很有必要！
甲午年(马)冬月十九 2015-1-9