某系统维护人员在执行一查询需要1个多小时才能执行完成，根本不能满足业务需求，通过应用系统日志找到了该SQL：
DEBUG: 2016-04-05 12:18:48,203: com.powerise.hygeia.framework.jdbclogger.PreparedStatementWrapper:
exec : 2456844ms at com.powerise.hygeia.biz.medicare.entity.EnLiquidateDetailInfo.getLiquidatePersonInfo(EnLiquidateDetailInfo.java:2319)
select rowno, hos_serial, reg_flag,
nvl((SELECT distinct t.policy_value
FROM fc_biz_policy t
WHERE t.policy_code = 'can_clinic_flag'
and t.valid_flag = '1'
and t.center_id = '430701'), (SELECT distinct t.policy_value
FROM fc_biz_policy t
WHERE t.policy_code =
'can_clinic_flag'
and t.valid_flag = '1'
and t.center_id = '0')) AS can_clinic_flag,
decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发', 'D', '危重', '') DISEASE_TYPE,
hospital_id, hospital_name, serial_no, biz_type, case_id, biz_stat,
name, sex, pers_type, begin_date, end_date, fin_date, finish_date,
reg_date, in_days, indi_id, insr_code, fin_staff, fin_man, corp_id,
idcard, district_code, office_grade,center_id,
(select t.special_code
from bs_corp_pres t
where t.indi_id = w.indi_id) as special_code, corp_name, disease,
in_area_name, in_dept_name,
decode(apply_content, '126', '转外住院', '127', '转外复查', '普通住院') as apply_content,
in_bed, bed_type, patient_id, remark, pos_code, reimburse_flag,
fin_disease, ic_no, treatment_type,
decode(reg_info || treatment_type, 'WD3120', '普通住院(转外住院)', 'WD2120', '普通住院(异地住院)', '1120', '普通住院(转诊转院)', 'F120', '普通住院(非首诊就诊)', 'WD1120', '普通住院(异地安置)', 'C120', '普通住院(首诊就诊)', (select treatment_name
from bs_treat_type
where treatment_type =
w.treatment_type
and center_id =
w.center_id)) as treatment_name,
nvl(pay_money_jd, 0) as pay_money_jd,
nvl(pay_money_xzf, 0) as pay_money_xzf, nvl(fees, 0) as fees,
foregift, nvl(pay_money_allself, 0) as pay_money_allself,
nvl(pay_money_self, 0) as pay_money_self,
nvl(pay_money_E00self, 0) as pay_money_E00self,
nvl(pay_money_Z00self, 0) as pay_money_Z00self,
nvl(pay_money_S00self, 0) as pay_money_S00self,
nvl(pay_money_S01self, 0) as pay_money_S01self,
nvl(pay_money_S01self, 0) as pay_money_S01self,
nvl(pay_money_C000self, 0) as pay_money_C000self,
nvl(pay_money_C001self, 0) as pay_money_C001self,
nvl(pay_money_C004self, 0) as pay_money_C004self,
nvl(pay_money_C006self, 0) as pay_money_C006self,
nvl(pay_money_C007self, 0) as pay_money_C007self,
nvl(pay_money_C007003self, 0) as pay_money_C007003self,
nvl(tc_money, 0) as tc_money, nvl(tc_money_xe, 0) as tc_money_xe,
nvl(hosp_pay, 0) as hosp_pay, nvl(hosp_prise, 0) as hosp_prise,
nvl(jmtc_money, 0) as jmtc_money, nvl(yw_money, 0) as yw_money,
nvl(jmyw_money, 0) as jmyw_money, nvl(acct_money, 0) as acct_money,
nvl(lx_money, 0) as lx_money, nvl(sy_money, 0) as sy_money,
nvl(fund_money, 0) as fund_money, nvl(fund_301,0) fund_301 ,nvl(fund_003,0) fund_003 ,nvl(qfx, 0) as qfx,
nvl(hosp_zf, 0) as hosp_zf, nvl(center_zf, 0) as center_zf,
nvl(yw_fund_money, 0) as yw_fund_money,
nvl(bc_fund_money, 0) as bc_fund_money, nvl(db_money, 0) as db_money,
nvl(YWSH_DB_MONEY, 0) as YWSH_DB_MONEY,
nvl(offi_money, 0) as offi_money, reg_man,
nvl(zhaogu_pay, 0) as zhaogu_pay, w.area_code, PAY_MONEY_DNZF,
medi_pay, medi_zfy, nvl(DB_MONEY_JUMIN, 0) as db_money_jumin
from (select rownum rowno, b.hos_serial, b.reg_flag, b.disease_type,
a.hospital_id, a.hospital_name, b.serial_no, b.biz_type,
b.case_id, g.biz_stat, b.name, (select bs.sex_name from bs_sex bs where bs.sex =b.sex) sex,
(select pp.pers_name from bs_person_type pp where pp.pers_type = b.pers_type_detail and pp.center_id = b.center_id) as pers_type,
to_char(b.begin_date, 'yyyy-mm-dd') begin_date,
to_char(b.end_date, 'yyyy-mm-dd') end_date,
to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss') fin_date,
to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss') finish_date,
b.indi_id, t.insr_code, b.fin_staff, b.fin_man, b.corp_id,
b.idcard,
to_char(b.reg_date, 'yyyy-mm-dd hh24:mi:ss') reg_date,
nvl(b.in_days, 0) in_days, b.district_code, b.center_id,
b.office_grade, b.corp_name, b.in_disease,
(select t.disease
from bs_disease t
where t.center_id = nvl(h.catalog_center, h.center_id)
and b.in_disease = t.icd) as disease, b.in_area_name,
b.in_dept_name, b.in_bed, b.bed_type, b.patient_id,
translate(b.remark, chr(13), '') remark, b.pos_code,
b.reimburse_flag,
(select q.disease
from bs_disease q
where q.center_id = nvl(h.catalog_center, h.center_id)
and b.fin_disease = q.icd) as fin_disease, 0 as foregift,
b.ic_no, b.treatment_type,
(select t.apply_content
from mt_apply t
where t.serial_apply = b.serial_apply) as apply_content,
b.reg_man, street.QYBM as area_code, T.FEES, T.PAY_MONEY_JD,
T.FUND_MONEY, T.FUND_301, T.FUND_003,T.QFX, T.HOSP_ZF, T.CENTER_ZF, T.YW_FUND_MONEY,
T.BC_FUND_MONEY, T.PAY_MONEY_ALLSELF, T.PAY_MONEY_SELF,
T.PAY_MONEY_E00SELF, T.PAY_MONEY_Z00SELF, T.PAY_MONEY_S00SELF,
T.PAY_MONEY_S01SELF, T.PAY_MONEY_S02SELF, T.PAY_MONEY_C000SELF,
T.PAY_MONEY_C001SELF, T.PAY_MONEY_C004SELF,
T.PAY_MONEY_C006SELF, T.PAY_MONEY_C007SELF,
T.PAY_MONEY_C007003SELF,
(T.DB_MONEY - t.YWSH_DB_MONEY) as DB_MONEY, t.YWSH_DB_MONEY,
T.TC_MONEY, T.TC_MONEY_XE, t.hosp_pay, t.hosp_prise,
T.JMTC_MONEY, T.YW_MONEY, T.JMYW_MONEY, T.ACCT_MONEY,
T.LX_MONEY, T.SY_MONEY, T.OFFI_MONEY, T.ZHAOGU_PAY,
T.PAY_MONEY_XZF, PAY_MONEY_DNZF, medi_pay, medi_zfy,
b.REG_INFO, T.DB_MONEY_JUMIN as DB_MONEY_JUMIN
from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h,
bs_insured j,
(select corp.CORP_ID, corp.AREA_CODE, st.QYBM
from bs_corp corp
inner join bs_country_street st
on corp.AREA_CODE = st.QYBM) street,
(SELECT B.hospital_id, B.serial_no, B.indi_id,
(select max(insr_code)
from bs_insured a
where a.indi_id = B.indi_id) insr_code,
SUM(c.real_pay) AS FEES,
SUM(DECODE(c.POLICY_ITEM_CODE, 'C000', c.REAL_PAY, 'C001', c.REAL_PAY, 'C002', c.REAL_PAY, 'C003', c.REAL_PAY, 'C004', c.REAL_PAY, 'C005', c.REAL_PAY, 'C006', c.REAL_PAY, 'C007', c.REAL_PAY, 0)) AS PAY_MONEY_JD,
SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '511', c.REAL_PAY, '202', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS FUND_MONEY,
SUM(DECODE(c.FUND_ID, '301', c.REAL_PAY, 0)) AS FUND_301,
SUM(DECODE(c.FUND_ID, '003', c.REAL_PAY, 0)) AS FUND_003,
SUM(DECODE(c.FUND_ID, '901', c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_FUND_MONEY,
SUM(DECODE(c.FUND_ID, '306', c.REAL_PAY, 0)) AS BC_FUND_MONEY,
SUM(DECODE(c.POLICY_ITEM_CODE, 'S01', c.REAL_PAY, 'S02', c.REAL_PAY, 0)) AS QFX,
SUM(DECODE(c.FUND_ID, '996', c.REAL_PAY, 0)) AS HOSP_ZF,
SUM(DECODE(c.FUND_ID, '998', c.REAL_PAY, 0)) AS CENTER_ZF,
SUM(DECODE(c.label_flag || c.FUND_ID, '101003', c.REAL_PAY, '101999', c.REAL_PAY, 0)) AS PAY_MONEY_ALLSELF,
SUM(DECODE(c.label_flag || c.FUND_ID, '102003', c.REAL_PAY, '102999', c.REAL_PAY, 0)) AS PAY_MONEY_SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'E00003', c.REAL_PAY, 'E00999', c.REAL_PAY, 0)) AS PAY_MONEY_E00SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'Z00003', c.REAL_PAY, 'Z00999', c.REAL_PAY, 0)) AS PAY_MONEY_Z00SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S00003', c.REAL_PAY, 'S00999', c.REAL_PAY, 0)) AS PAY_MONEY_S00SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S01003', c.REAL_PAY, 'S01999', c.REAL_PAY, 0)) AS PAY_MONEY_S01SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S02003', c.REAL_PAY, 'S02999', c.REAL_PAY, 0)) AS PAY_MONEY_S02SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C000003', c.REAL_PAY, 'C000999', c.REAL_PAY, 0)) AS PAY_MONEY_C000SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C001003', c.REAL_PAY, 'C001999', c.REAL_PAY, 'C002003', c.REAL_PAY, 'C002999', c.REAL_PAY, 'C003003', c.REAL_PAY, 'C003999', c.REAL_PAY, 0)) AS PAY_MONEY_C001SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C004003', c.REAL_PAY, 'C004999', c.REAL_PAY, 'C005003', c.REAL_PAY, 'C005999', c.REAL_PAY, 0)) AS PAY_MONEY_C004SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C006003', c.REAL_PAY, 'C006999', c.REAL_PAY, 'C007003', c.REAL_PAY, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C006SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C007SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C007003', c.REAL_PAY, 0)) AS PAY_MONEY_C007003SELF,
SUM(DECODE(c.FUND_ID, '201', c.REAL_PAY, 0)) AS DB_MONEY,
0 AS YWSH_DB_MONEY,
SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY,
case
when nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0) >= 0 then
nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0)
else
0
end hosp_pay,
case
when nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0) >= 0 then
0
else
-nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0)
end hosp_prise,
SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY_XE,
SUM(DECODE(c.FUND_ID, '801', c.REAL_PAY, 0)) AS JMTC_MONEY,
SUM(DECODE(c.FUND_ID, '901', c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_MONEY,
SUM(DECODE(c.FUND_ID, '802', c.REAL_PAY, 0)) AS JMYW_MONEY,
SUM(DECODE(c.FUND_ID, '003', c.REAL_PAY, 0)) AS ACCT_MONEY,
SUM(DECODE(c.FUND_ID, '202', c.REAL_PAY, 0)) AS LX_MONEY,
SUM(DECODE(c.FUND_ID, '511', c.REAL_PAY, 0)) AS SY_MONEY,
SUM(DECODE(c.FUND_ID, '301', c.REAL_PAY, 0)) AS OFFI_MONEY,
SUM(DECODE(c.FUND_ID, '401', c.REAL_PAY, 0)) AS ZHAOGU_PAY,
SUM(DECODE(c.FUND_ID, '999', c.REAL_PAY, 0)) AS PAY_MONEY_XZF,
SUM((CASE
WHEN C.POLICY_ITEM_CODE IN
('C000', 'C001', 'C002', 'C003', 'C004') AND
(C.fund_id = '999' OR C.fund_id = '003') THEN
C.REAL_PAY
ELSE
0
END)) AS PAY_MONEY_DNZF,
(select nvl(nvl(sum(ss.all_self_money), 0) +
nvl(sum(ss.part_self_money), 0), 0) as not_fund
from mt_fee_stat_fin ss
where ss.hospital_id = b.hospital_id
and ss.serial_no = b.serial_no
and ss.valid_flag = '1'
and ss.stat_type in ('001', '002', '003')) as medi_pay,
(select nvl(sum(ss.money), 0) as medi_zfy
from mt_fee_stat_fin ss
where ss.hospital_id = b.hospital_id
and ss.serial_no = b.serial_no
and ss.valid_flag = '1'
and ss.stat_type in ('001', '002', '003')) as medi_zfy,
SUM(DECODE(c.FUND_ID, '803', c.REAL_PAY, 0)) AS DB_MONEY_JUMIN
FROM MT_BIZ_FIN B, MT_PAY_RECORD_FIN C, BS_BIZTYPE G,
BS_INSURED J
WHERE B.HOSPITAL_ID = C.HOSPITAL_ID
AND B.SERIAL_NO = C.SERIAL_NO
AND B.CENTER_ID = G.CENTER_ID
AND B.BIZ_TYPE = G.BIZ_TYPE
AND B.VALID_FLAG = '1'
AND C.VALID_FLAG = '1'
and b.indi_id = j.indi_id
and b.hospital_id || b.serial_no in
(select hospital_id || serial_no
from pm_account_biz
where month_decl_sn in
(select rela_decl_sn
from pm_bill
where outpay_bill_no = '23') and ((biz_flag = '18' and biz_type = '12') or biz_type <>'12' ))
GROUP BY b.hospital_id, b.serial_no, b.indi_id) T
where a.hospital_id = b.hospital_id
and b.center_id = g.center_id
and b.biz_type = g.biz_type
and b.center_id = h.center_id(+)
and b.valid_flag = '1'
and b.indi_id = j.indi_id
and b.CORP_ID = street.CORP_ID(+)
AND B.HOSPITAL_ID = T.HOSPITAL_ID
AND B.SERIAL_NO = T.SERIAL_NO
order by rowno) w
从上面的信息可以看到执行花费了2456844ms。从awr报告可以看到主要等待事件也是属于I/O
从sql报告来看
执行sql调整任务
SQL> @C:\oracle\product\10.2.0\client_1\rdbms\admin\sqltrpt.sql
Cannot SET TAB
Cannot SET LONGCHUNKSIZE
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
newl
---------
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
arb763ht1q5j0 17462.1402 select hospital_id, serial_no, serial_f
2m613a70mtp33 14098.6959 call usp_pay_account_declare(:1,:2,:3,:4,:5,:6,:7,:8,:9
ah4mrap4wpyk3 11941.5544 INSERT INTO BS_CATALOG_MATCH (CENTER_ID, SERIAL_MATCH,
fj88hvyfjf7u3 7571.78878 insert into mt_biz_fin( hospital_id, serial_no, fee_ba
17janqkjd2bx3 7172.00961 select hospital_id, serial_no, fee_ba
0skcw15cj8kg7 5772.45381 select rowno, hos_serial, reg_flag, nvl((SELECT
691vbnfyxyvr3 5277.95397 update bs_mdi_indi_acc set last_balance = last_balanc
83h4yucvjnyap 5228.42888 UPDATE PM_ACCOUNT_BIZ A SET A.YEAR_PAY_TYPE = A.FINAL_P
37j74mj2t6d3g 5055.72843 insert into mt_biz_scene_fin(hospital_id, serial_no, fe
dfrc1yjumpkgb 3495.65936 insert into mt_pay_record_fin(hospital_id, serial_no, f
07q1m7pybb7sn 3450.10698 insert into BS_HOSP_LOGIN_LOGFIN select * from BS_HOSP_
35ckgb9fcpbpd 3349.30750 select nvl(sum(decode(a.total_type,'C0000',nvl(a.sum_y
1urbbnr5sc8u3 3010.5935 select a.biz_type,a.special_code,a.indi_id, a.reimburse
bp5ppsp50z4cg 2728.77444 insert into bs_icfee (serial_ic, card_no, card_type, i
6agr1962w3wfw 2667.30935 insert into bs_biztotal_record (id,indi_id,hospital_id,
newl
---------
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- --------------------------------------------------------------------------------
41w2uhn9uukx2 46992.5939 BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_08',
0skcw15cj8kg7 40589.7354 select rowno, hos_serial, reg_flag, nvl((SELECT
d2nqy2m0d6k02 25714.3942 select rowno, hos_serial, reg_flag, nvl((SELECT
57vdxh1xps2r7 21134.4932 BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_03',
dbknuva8j82f6 9400.07834 DECLARE job BINARY_INTEGER := :job; next_date DATE := :
b6usrg82hwsa3 7914.57973 call dbms_stats.gather_database_stats_job_proc ( )
ga24g7vg22nh5 5493.49117 select distinct a.busi_bill_sn,a.audit_flag,a.make_bil
5vagsa3xhxduu 5212.08508 BEGIN usp_kettle_exec_proc ( :1, :2, :3, :4, :5); E
arb763ht1q5j0 4786.13909 select hospital_id, serial_no, serial_f
gdbumbxg088f2 4220.72077 select zf.district_code,zf.center_id , sum(zf.zzf) as "
4c3s78cg7qgaw 3534.64534 INSERT INTO ZXJ_YLJ_QS (ND, CENTER_ID, DISTRICT_CODE, R
2m613a70mtp33 3491.63022 call usp_pay_account_declare(:1,:2,:3,:4,:5,:6,:7,:8,:9
datjs0356h3n4 3351.04771 select w.hospital_id,t.hospital_name,sum(mzrc) mzrc,sum
9su2cmt6gg3rf 3336.44954 INSERT INTO ZXJ_YLJ_QS (ND, CENTER_ID, DISTRICT_CODE, R
g2xvxhuqb3mm7 3323.54692 select tab_pay.zj_code as corp_five_no,tab_pay.corp_id,
newl
---------
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Sql Id specified: 0skcw15cj8kg7
Tune the sql
~~~~~~~~~~~~
err
---------
0
task_name
---------
TASK_39762
err
---------
0
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_39762
Tuning Task Owner : INSUR_CHANGDE
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 04/05/2016 16:22:22
Completed at : 04/05/2016 16:32:24
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID : 0skcw15cj8kg7
SQL Text : select rowno, hos_serial, reg_flag,
nvl((SELECT distinct t.policy_value
FROM fc_biz_policy t
WHERE t.policy_code = 'can_clinic_flag'
and t.valid_flag = '1'
and t.center_id = '430701'), (SELECT distinct
t.policy_value
FROM fc_biz_policy t
WHERE t.policy_code =
'can_clinic_flag'
and t.valid_flag = '1'
and t.center_id = '0')) AS can_clinic_flag,
decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发',
'D', '危重', '') DISEASE_TYPE,
hospital_id, hospital_name, serial_no, biz_type, case_id,
biz_stat,
name, sex, pers_type, begin_date, end_date, fin_date,
finish_date,
reg_date, in_days, indi_id, insr_code, fin_staff,
fin_man, corp_id,
idcard, district_code, office_grade,center_id,
(select t.special_code
from bs_corp_pres t
where t.indi_id = w.indi_id) as special_code,
corp_name, disease,
in_area_name, in_dept_name,
decode(apply_content, '126', '转外住院', '127', '转外复查',
'普通住院') as apply_content,
in_bed, bed_type, patient_id, remark, pos_code,
reimburse_flag,
fin_disease, ic_no, treatment_type,
decode(reg_info || treatment_type, 'WD3120',
'普通住院(转外住院)', 'WD2120', '普通住院(异地住院)', '1120', '普通住院(转诊转院)',
'F120', '普通住院(非首诊就诊)', 'WD1120', '普通住院(异地安置)', 'C120',
'普通住院(首诊就诊)', (select treatment_name
from bs_treat_type
where treatment_type =
w.treatment_type
and center_id =
w.center_id)) as treatment_name,
nvl(pay_money_jd, 0) as pay_money_jd,
nvl(pay_money_xzf, 0) as pay_money_xzf, nvl(fees, 0) as
fees,
foregift, nvl(pay_money_allself, 0) as pay_money_allself,
nvl(pay_money_self, 0) as pay_money_self,
nvl(pay_money_E00self, 0) as pay_money_E00self,
nvl(pay_money_Z00self, 0) as pay_money_Z00self,
nvl(pay_money_S00self, 0) as pay_money_S00self,
nvl(pay_money_S01self, 0) as pay_money_S01self,
nvl(pay_money_S01self, 0) as pay_money_S01self,
nvl(pay_money_C000self, 0) as pay_money_C000self,
nvl(pay_money_C001self, 0) as pay_money_C001self,
nvl(pay_money_C004self, 0) as pay_money_C004self,
nvl(pay_money_C006self, 0) as pay_money_C006self,
nvl(pay_money_C007self, 0) as pay_money_C007self,
nvl(pay_money_C007003self, 0) as pay_money_C007003self,
nvl(tc_money, 0) as tc_money, nvl(tc_money_xe, 0) as
tc_money_xe,
nvl(hosp_pay, 0) as hosp_pay, nvl(hosp_prise, 0) as
hosp_prise,
nvl(jmtc_money, 0) as jmtc_money, nvl(yw_money, 0) as
yw_money,
nvl(jmyw_money, 0) as jmyw_money, nvl(acct_money, 0) as
acct_money,
nvl(lx_money, 0) as lx_money, nvl(sy_money, 0) as
sy_money,
nvl(fund_money, 0) as fund_money, nvl(fund_301,0)
fund_301 ,nvl(fund_003,0) fund_003 ,nvl(qfx, 0) as qfx,
nvl(hosp_zf, 0) as hosp_zf, nvl(center_zf, 0) as
center_zf,
nvl(yw_fund_money, 0) as yw_fund_money,
nvl(bc_fund_money, 0) as bc_fund_money, nvl(db_money, 0)
as db_money,
nvl(YWSH_DB_MONEY, 0) as YWSH_DB_MONEY,
nvl(offi_money, 0) as offi_money, reg_man,
nvl(zhaogu_pay, 0) as zhaogu_pay, w.area_code,
PAY_MONEY_DNZF,
medi_pay, medi_zfy, nvl(DB_MONEY_JUMIN, 0) as
db_money_jumin
from (select rownum rowno, b.hos_serial, b.reg_flag,
b.disease_type,
a.hospital_id, a.hospital_name, b.serial_no,
b.biz_type,
b.case_id, g.biz_stat, b.name, (select
bs.sex_name from bs_sex bs where bs.sex =b.sex) sex,
(select pp.pers_name from bs_person_type pp
where pp.pers_type = b.pers_type_detail and pp.center_id =
b.center_id) as pers_type,
to_char(b.begin_date, 'yyyy-mm-dd') begin_date,
to_char(b.end_date, 'yyyy-mm-dd') end_date,
to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss')
fin_date,
to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss')
finish_date,
b.indi_id, t.insr_code, b.fin_staff, b.fin_man,
b.corp_id,
b.idcard,
to_char(b.reg_date, 'yyyy-mm-dd hh24:mi:ss')
reg_date,
nvl(b.in_days, 0) in_days, b.district_code,
b.center_id,
b.office_grade, b.corp_name, b.in_disease,
(select t.disease
from bs_disease t
where t.center_id = nvl(h.catalog_center,
h.center_id)
and b.in_disease = t.icd) as disease,
b.in_area_name,
b.in_dept_name, b.in_bed, b.bed_type,
b.patient_id,
translate(b.remark, chr(13), '') remark,
b.pos_code,
b.reimburse_flag,
(select q.disease
from bs_disease q
where q.center_id = nvl(h.catalog_center,
h.center_id)
and b.fin_disease = q.icd) as fin_disease,
0 as foregift,
b.ic_no, b.treatment_type,
(select t.apply_content
from mt_apply t
where t.serial_apply = b.serial_apply) as
apply_content,
b.reg_man, street.QYBM as area_code, T.FEES,
T.PAY_MONEY_JD,
T.FUND_MONEY, T.FUND_301, T.FUND_003,T.QFX,
T.HOSP_ZF, T.CENTER_ZF, T.YW_FUND_MONEY,
T.BC_FUND_MONEY, T.PAY_MONEY_ALLSELF,
T.PAY_MONEY_SELF,
T.PAY_MONEY_E00SELF, T.PAY_MONEY_Z00SELF,
T.PAY_MONEY_S00SELF,
T.PAY_MONEY_S01SELF, T.PAY_MONEY_S02SELF,
T.PAY_MONEY_C000SELF,
T.PAY_MONEY_C001SELF, T.PAY_MONEY_C004SELF,
T.PAY_MONEY_C006SELF, T.PAY_MONEY_C007SELF,
T.PAY_MONEY_C007003SELF,
(T.DB_MONEY - t.YWSH_DB_MONEY) as DB_MONEY,
t.YWSH_DB_MONEY,
T.TC_MONEY, T.TC_MONEY_XE, t.hosp_pay,
t.hosp_prise,
T.JMTC_MONEY, T.YW_MONEY, T.JMYW_MONEY,
T.ACCT_MONEY,
T.LX_MONEY, T.SY_MONEY, T.OFFI_MONEY,
T.ZHAOGU_PAY,
T.PAY_MONEY_XZF, PAY_MONEY_DNZF, medi_pay,
medi_zfy,
b.REG_INFO, T.DB_MONEY_JUMIN as DB_MONEY_JUMIN
from bs_hospital a, bs_biztype g, mt_biz_fin b,
bs_center h,
bs_insured j,
(select corp.CORP_ID, corp.AREA_CODE, st.QYBM
from bs_corp corp
inner join bs_country_street st
on corp.AREA_CODE = st.QYBM) street,
(SELECT B.hospital_id, B.serial_no, B.indi_id,
(select max(insr_code)
from bs_insured a
where a.indi_id = B.indi_id)
insr_code,
SUM(c.real_pay) AS FEES,
SUM(DECODE(c.POLICY_ITEM_CODE, 'C000',
c.REAL_PAY, 'C001', c.REAL_PAY, 'C002', c.REAL_PAY, 'C003',
c.REAL_PAY, 'C004', c.REAL_PAY, 'C005', c.REAL_PAY, 'C006',
c.REAL_PAY, 'C007', c.REAL_PAY, 0)) AS PAY_MONEY_JD,
SUM(DECODE(c.FUND_ID, '001',
c.REAL_PAY, '511', c.REAL_PAY, '202', c.REAL_PAY, '801',
c.REAL_PAY, 0)) AS FUND_MONEY,
SUM(DECODE(c.FUND_ID, '301',
c.REAL_PAY, 0)) AS FUND_301,
SUM(DECODE(c.FUND_ID, '003',
c.REAL_PAY, 0)) AS FUND_003,
SUM(DECODE(c.FUND_ID, '901',
c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_FUND_MONEY,
SUM(DECODE(c.FUND_ID, '306',
c.REAL_PAY, 0)) AS BC_FUND_MONEY,
SUM(DECODE(c.POLICY_ITEM_CODE, 'S01',
c.REAL_PAY, 'S02', c.REAL_PAY, 0)) AS QFX,
SUM(DECODE(c.FUND_ID, '996',
c.REAL_PAY, 0)) AS HOSP_ZF,
SUM(DECODE(c.FUND_ID, '998',
c.REAL_PAY, 0)) AS CENTER_ZF,
SUM(DECODE(c.label_flag || c.FUND_ID,
'101003', c.REAL_PAY, '101999', c.REAL_PAY, 0)) AS
PAY_MONEY_ALLSELF,
SUM(DECODE(c.label_flag || c.FUND_ID,
'102003', c.REAL_PAY, '102999', c.REAL_PAY, 0)) AS
PAY_MONEY_SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'E00003', c.REAL_PAY, 'E00999', c.REAL_PAY, 0)) AS
PAY_MONEY_E00SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'Z00003', c.REAL_PAY, 'Z00999', c.REAL_PAY, 0)) AS
PAY_MONEY_Z00SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'S00003', c.REAL_PAY, 'S00999', c.REAL_PAY, 0)) AS
PAY_MONEY_S00SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'S01003', c.REAL_PAY, 'S01999', c.REAL_PAY, 0)) AS
PAY_MONEY_S01SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'S02003', c.REAL_PAY, 'S02999', c.REAL_PAY, 0)) AS
PAY_MONEY_S02SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'C000003', c.REAL_PAY, 'C000999', c.REAL_PAY, 0)) AS
PAY_MONEY_C000SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'C001003', c.REAL_PAY, 'C001999', c.REAL_PAY,
'C002003', c.REAL_PAY, 'C002999', c.REAL_PAY, 'C003003',
c.REAL_PAY, 'C003999', c.REAL_PAY, 0)) AS PAY_MONEY_C001SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'C004003', c.REAL_PAY, 'C004999', c.REAL_PAY,
'C005003', c.REAL_PAY, 'C005999', c.REAL_PAY, 0)) AS
PAY_MONEY_C004SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'C006003', c.REAL_PAY, 'C006999', c.REAL_PAY,
'C007003', c.REAL_PAY, 'C007999', c.REAL_PAY, 0)) AS
PAY_MONEY_C006SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C007SELF,
SUM(DECODE(c.policy_item_code ||
c.FUND_ID, 'C007003', c.REAL_PAY, 0)) AS PAY_MONEY_C007003SELF,
SUM(DECODE(c.FUND_ID, '201',
c.REAL_PAY, 0)) AS DB_MONEY,
0 AS YWSH_DB_MONEY,
SUM(DECODE(c.FUND_ID, '001',
c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY,
case
when nvl(sum(decode(c.fund_id, '996',
nvl(c.real_pay, 0), 0)), 0) >= 0 then
nvl(sum(decode(c.fund_id, '996',
nvl(c.real_pay, 0), 0)), 0)
else
0
end hosp_pay,
case
when nvl(sum(decode(c.fund_id, '996',
nvl(c.real_pay, 0), 0)), 0) >= 0 then
0
else
-nvl(sum(decode(c.fund_id, '996',
nvl(c.real_pay, 0), 0)), 0)
end hosp_prise,
SUM(DECODE(c.FUND_ID, '001',
c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY_XE,
SUM(DECODE(c.FUND_ID, '801',
c.REAL_PAY, 0)) AS JMTC_MONEY,
SUM(DECODE(c.FUND_ID, '901',
c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_MONEY,
SUM(DECODE(c.FUND_ID, '802',
c.REAL_PAY, 0)) AS JMYW_MONEY,
SUM(DECODE(c.FUND_ID, '003',
c.REAL_PAY, 0)) AS ACCT_MONEY,
SUM(DECODE(c.FUND_ID, '202',
c.REAL_PAY, 0)) AS LX_MONEY,
SUM(DECODE(c.FUND_ID, '511',
c.REAL_PAY, 0)) AS SY_MONEY,
SUM(DECODE(c.FUND_ID, '301',
c.REAL_PAY, 0)) AS OFFI_MONEY,
SUM(DECODE(c.FUND_ID, '401',
c.REAL_PAY, 0)) AS ZHAOGU_PAY,
SUM(DECODE(c.FUND_ID, '999',
c.REAL_PAY, 0)) AS PAY_MONEY_XZF,
SUM((CASE
WHEN C.POLICY_ITEM_CODE IN
('C000', 'C001', 'C002',
'C003', 'C004') AND
(C.fund_id = '999' OR
C.fund_id = '003') THEN
C.REAL_PAY
ELSE
0
END)) AS PAY_MONEY_DNZF,
(select nvl(nvl(sum(ss.all_self_money),
0) +
nvl(sum(ss.part_self_money), 0), 0) as not_fund
from mt_fee_stat_fin ss
where ss.hospital_id = b.hospital_id
and ss.serial_no = b.serial_no
and ss.valid_flag = '1'
and ss.stat_type in ('001', '002',
'003')) as medi_pay,
(select nvl(sum(ss.money), 0) as
medi_zfy
from mt_fee_stat_fin ss
where ss.hospital_id = b.hospital_id
and ss.serial_no = b.serial_no
and ss.valid_flag = '1'
and ss.stat_type in ('001', '002',
'003')) as medi_zfy,
SUM(DECODE(c.FUND_ID, '803',
c.REAL_PAY, 0)) AS DB_MONEY_JUMIN
FROM MT_BIZ_FIN B, MT_PAY_RECORD_FIN C,
BS_BIZTYPE G,
BS_INSURED J
WHERE B.HOSPITAL_ID = C.HOSPITAL_ID
AND B.SERIAL_NO = C.SERIAL_NO
AND B.CENTER_ID = G.CENTER_ID
AND B.BIZ_TYPE = G.BIZ_TYPE
AND B.VALID_FLAG = '1'
AND C.VALID_FLAG = '1'
and b.indi_id = j.indi_id
and b.hospital_id || b.serial_no in
(select hospital_id || serial_no
from pm_account_biz
where month_decl_sn in
(select rela_decl_sn
from pm_bill
where outpay_bill_no = '23')
and ((biz_flag = '18' and biz_type = '12') or biz_type <>'12' ))
GROUP BY b.hospital_id, b.serial_no,
b.indi_id) T
where a.hospital_id = b.hospital_id
and b.center_id = g.center_id
and b.biz_type = g.biz_type
and b.center_id = h.center_id(+)
and b.valid_flag = '1'
and b.indi_id = j.indi_id
and b.CORP_ID = street.CORP_ID(+)
AND B.HOSPITAL_ID = T.HOSPITAL_ID
AND B.SERIAL_NO = T.SERIAL_NO
order by rowno) w
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_39762',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
调整前的执行计划
1- Original With Adjusted Cost
------------------------------
Plan hash value: 462538689
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 648G| 923T| | 18G (2)|999:59:59 |
| 1 | HASH UNIQUE | | 1 | 31 | | 2 (50)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | FC_BIZ_POLICY | 1 | 31 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PK_FC_BIZ_POLICY | 1 | | | 1 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 1 | 31 | | 2 (50)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID | FC_BIZ_POLICY | 1 | 31 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_FC_BIZ_POLICY | 1 | | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | BS_SEX | 1 | 6 | | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_BS_SEX | 1 | | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | BS_PERSON_TYPE | 1 | 18 | | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_BS_PERSON_TYPE | 1 | | | 1 (0)| 00:00:01 |
| 11 | SORT AGGREGATE | | 1 | 18 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | BS_INSURED | 1 | 18 | | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | BS_CORP_PRES | 1 | 8 | | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | INDEX_BS_CORP_PRES_INDI | 1 | | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | | 1 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | MT_APPLY | 1 | 9 | | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_MT_APPLY | 1 | | | 1 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | | 1 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | BS_TREAT_TYPE | 1 | 23 | | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_BS_TREAT_TYPE | 1 | | | 1 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 34 | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | MT_FEE_STAT_FIN | 1 | 34 | | 1 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | PK_MT_FEE_STAT_FIN | 1 | | | 1 (0)| 00:00:01 |
| 27 | SORT AGGREGATE | | 1 | 31 | | | |
|* 28 | TABLE ACCESS BY INDEX ROWID | MT_FEE_STAT_FIN | 1 | 31 | | 1 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | PK_MT_FEE_STAT_FIN | 1 | | | 1 (0)| 00:00:01 |
| 30 | VIEW | | 648G| 923T| | 18G (2)|999:59:59 |
| 31 | SORT ORDER BY | | 648G| 516T| 1072T| 18G (2)|999:59:59 |
| 32 | COUNT | | | | | | |
|* 33 | HASH JOIN RIGHT OUTER | | 648G| 516T| | 9061M (2)|999:59:59 |
| 34 | VIEW | | 1 | 12 | | 117 (2)| 00:00:02 |
|* 35 | HASH JOIN | | 1 | 18 | | 117 (2)| 00:00:02 |
| 36 | TABLE ACCESS FULL | BS_COUNTRY_STREET | 1 | 7 | | 2 (0)| 00:00:01 |
|* 37 | TABLE ACCESS FULL | BS_CORP | 9523 | 102K| | 114 (1)| 00:00:02 |
|* 38 | HASH JOIN | | 648G| 509T| | 9058M (2)|999:59:59 |
| 39 | TABLE ACCESS FULL | BS_BIZTYPE | 97 | 1164 | | 3 (0)| 00:00:01 |
|* 40 | HASH JOIN RIGHT OUTER | | 648G| 502T| | 9054M (2)|999:59:59 |
| 41 | TABLE ACCESS FULL | BS_CENTER | 12 | 168 | | 3 (0)| 00:00:01 |
| 42 | MERGE JOIN | | 648G| 493T| | 9051M (2)|999:59:59 |
| 43 | NESTED LOOPS | | 809G| 592T| | 9051M (2)|999:59:59 |
| 44 | MERGE JOIN | | 808G| 587T| | 9042M (2)|999:59:59 |
| 45 | SORT JOIN | | 819G| 427T| | 9040M (2)|999:59:59 |
| 46 | VIEW | | 819G| 427T| | 9040M (2)|999:59:59 |
| 47 | HASH GROUP BY | | 819G| 93T| 200T| 9040M (2)|999:59:59 |
|* 48 | HASH JOIN | | 819G| 93T| 114M| 5209K (86)| 17:21:55 |
| 49 | VIEW | VW_NSO_1 | 3750K| 71M| | 41283 (1)| 00:08:16 |
| 50 | HASH UNIQUE | | 3750K| 146M| 404M| 41283 (1)| 00:08:16 |
|* 51 | TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ | 205 | 6560 | | 9 (0)| 00:00:01 |
| 52 | NESTED LOOPS | | 3750K| 146M| | 1405 (2)| 00:00:17 |
|* 53 | TABLE ACCESS FULL | PM_BILL | 31 | 279 | | 1119 (2)| 00:00:14 |
|* 54 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_MONTH | 213 | | | 1 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 21M| 2208M| | 598K (2)| 01:59:45 |
|* 56 | HASH JOIN | | 21M| 2080M| | 598K (1)| 01:59:42 |
| 57 | INDEX FULL SCAN | PK_BS_BIZTYPE | 97 | 970 | | 1 (0)| 00:00:01 |
|* 58 | HASH JOIN | | 21M| 1872M| 1089M| 598K (1)| 01:59:40 |
|* 59 | TABLE ACCESS FULL | MT_BIZ_FIN | 21M| 838M| | 209K (2)| 00:41:54 |
|* 60 | TABLE ACCESS FULL | MT_PAY_RECORD_FIN | 62M| 3003M| | 149K (2)| 00:29:52 |
|* 61 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | | 1 (0)| 00:00:01 |
|* 62 | SORT JOIN | | 21M| 4715M| 12G| 1282K (1)| 04:16:26 |
|* 63 | TABLE ACCESS FULL | MT_BIZ_FIN | 21M| 4715M| | 209K (2)| 00:41:57 |
|* 64 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | | 1 (0)| 00:00:01 |
|* 65 | SORT JOIN | | 1325 | 43725 | | 3 (34)| 00:00:01 |
| 66 | INDEX FULL SCAN | IDX_BS_HOSPITAL_NAME | 1325 | 43725 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."VALID_FLAG"='1')
3 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='430701')
filter("T"."CENTER_ID"='430701')
5 - filter("T"."VALID_FLAG"='1')
6 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='0')
filter("T"."CENTER_ID"='0')
8 - access("BS"."SEX"=TO_NUMBER(:B1))
10 - access("PP"."PERS_TYPE"=TO_NUMBER(:B1) AND "PP"."CENTER_ID"=:B2)
13 - access("A"."INDI_ID"=:B1)
15 - access("T"."INDI_ID"=:B1)
17 - access("T"."CENTER_ID"=NVL(:B1,:B2) AND "T"."ICD"=:B3)
19 - access("T"."SERIAL_APPLY"=:B1)
21 - access("Q"."CENTER_ID"=NVL(:B1,:B2) AND "Q"."ICD"=:B3)
23 - access("TREATMENT_TYPE"=:B1 AND "CENTER_ID"=:B2)
25 - filter("SS"."VALID_FLAG"='1')
26 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
filter("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003')
28 - filter("SS"."VALID_FLAG"='1')
29 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
filter("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003')
33 - access("B"."CORP_ID"="STREET"."CORP_ID"(+))
35 - access("CORP"."AREA_CODE"="ST"."QYBM")
37 - filter("CORP"."AREA_CODE" IS NOT NULL)
38 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
40 - access("B"."CENTER_ID"="H"."CENTER_ID"(+))
48 - access("$nso_col_1"="B"."HOSPITAL_ID"||"B"."SERIAL_NO")
51 - filter("BIZ_TYPE"<>'12' OR "BIZ_FLAG"=18 AND "BIZ_TYPE"='12')
53 - filter("OUTPAY_BILL_NO"=23)
54 - access("MONTH_DECL_SN"="RELA_DECL_SN")
56 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
58 - access("B"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "B"."SERIAL_NO"="C"."SERIAL_NO")
59 - filter("B"."VALID_FLAG"='1')
60 - filter("C"."VALID_FLAG"='1')
61 - access("B"."INDI_ID"="J"."INDI_ID")
62 - access("B"."HOSPITAL_ID"="T"."HOSPITAL_ID" AND "B"."SERIAL_NO"="T"."SERIAL_NO")
filter("B"."SERIAL_NO"="T"."SERIAL_NO" AND "B"."HOSPITAL_ID"="T"."HOSPITAL_ID")
63 - filter("B"."VALID_FLAG"='1')
64 - access("B"."INDI_ID"="J"."INDI_ID")
65 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
filter("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
使用调整任务生成的sql profile优化后的执行计划
2- Using SQL Profile
--------------------
Plan hash value: 3772053484
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20043 | 29M| | 309K (1)| 01:01:52 |
| 1 | HASH UNIQUE | | 1 | 31 | | 2 (50)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | FC_BIZ_POLICY | 1 | 31 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PK_FC_BIZ_POLICY | 1 | | | 1 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 1 | 31 | | 2 (50)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID | FC_BIZ_POLICY | 1 | 31 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_FC_BIZ_POLICY | 1 | | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | BS_SEX | 1 | 6 | | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_BS_SEX | 1 | | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | BS_PERSON_TYPE | 1 | 18 | | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_BS_PERSON_TYPE | 1 | | | 1 (0)| 00:00:01 |
| 11 | SORT AGGREGATE | | 1 | 18 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | BS_INSURED | 1 | 18 | | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | BS_CORP_PRES | 1 | 8 | | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | INDEX_BS_CORP_PRES_INDI | 1 | | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | | 1 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | MT_APPLY | 1 | 9 | | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_MT_APPLY | 1 | | | 1 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | | 1 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | BS_TREAT_TYPE | 1 | 23 | | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_BS_TREAT_TYPE | 1 | | | 1 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 34 | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | MT_FEE_STAT_FIN | 1 | 34 | | 1 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | PK_MT_FEE_STAT_FIN | 1 | | | 1 (0)| 00:00:01 |
| 27 | SORT AGGREGATE | | 1 | 31 | | | |
|* 28 | TABLE ACCESS BY INDEX ROWID | MT_FEE_STAT_FIN | 1 | 31 | | 1 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | PK_MT_FEE_STAT_FIN | 1 | | | 1 (0)| 00:00:01 |
| 30 | VIEW | | 20043 | 29M| | 309K (1)| 01:01:52 |
| 31 | SORT ORDER BY | | 20043 | 16M| 34M| 309K (1)| 01:01:52 |
| 32 | COUNT | | | | | | |
| 33 | NESTED LOOPS | | 20043 | 16M| | 305K (1)| 01:01:08 |
|* 34 | HASH JOIN | | 20011 | 16M| | 305K (1)| 01:01:07 |
| 35 | TABLE ACCESS FULL | BS_BIZTYPE | 97 | 1164 | | 3 (0)| 00:00:01 |
|* 36 | HASH JOIN RIGHT OUTER | | 20011 | 16M| | 305K (1)| 01:01:07 |
| 37 | TABLE ACCESS FULL | BS_CENTER | 12 | 168 | | 3 (0)| 00:00:01 |
|* 38 | HASH JOIN RIGHT OUTER | | 20011 | 16M| | 305K (1)| 01:01:07 |
| 39 | VIEW | | 1 | 12 | | 117 (2)| 00:00:02 |
|* 40 | HASH JOIN | | 1 | 18 | | 117 (2)| 00:00:02 |
| 41 | TABLE ACCESS FULL | BS_COUNTRY_STREET | 1 | 7 | | 2 (0)| 00:00:01 |
|* 42 | TABLE ACCESS FULL | BS_CORP | 9523 | 102K| | 114 (1)| 00:00:02 |
|* 43 | HASH JOIN | | 20011 | 15M| | 305K (1)| 01:01:06 |
| 44 | INDEX FULL SCAN | IDX_BS_HOSPITAL_NAME | 1325 | 43725 | | 2 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 225 | | 1 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 25021 | 19M| | 305K (1)| 01:01:06 |
| 47 | VIEW | | 25349 | 13M| | 295K (1)| 00:59:04 |
| 48 | HASH GROUP BY | | 25349 | 1460K| | 295K (1)| 00:59:04 |
| 49 | VIEW | | 25349 | 1460K| | 295K (1)| 00:59:04 |
| 50 | HASH UNIQUE | | 25349 | 4876K| 10M| 295K (1)| 00:59:04 |
| 51 | NESTED LOOPS | | 25349 | 4876K| | 294K (1)| 00:58:51 |
| 52 | NESTED LOOPS | | 25309 | 4424K| | 294K (1)| 00:58:51 |
| 53 | NESTED LOOPS | | 25309 | 3880K| | 294K (1)| 00:58:51 |
|* 54 | HASH JOIN | | 25502 | 2365K| 189M| 289K (1)| 00:57:50 |
|* 55 | TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ | 205 | 6560 | | 9 (0)| 00:00:01 |
| 56 | NESTED LOOPS | | 3750K| 146M| | 1405 (2)| 00:00:17 |
|* 57 | TABLE ACCESS FULL | PM_BILL | 31 | 279 | | 1119 (2)| 00:00:14 |
|* 58 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_MONTH | 213 | | | 1 (0)| 00:00:01 |
|* 59 | TABLE ACCESS FULL | MT_BIZ_FIN | 21M| 1131M| | 209K (2)| 00:41:54 |
|* 60 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 62 | | 1 (0)| 00:00:01 |
|* 61 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | | 1 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | PK_BS_BIZTYPE | 1 | 22 | | 1 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 18 | | 1 (0)| 00:00:01 |
|* 64 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | | 1 (0)| 00:00:01 |
|* 65 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."VALID_FLAG"='1')
3 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='430701')
filter("T"."CENTER_ID"='430701')
5 - filter("T"."VALID_FLAG"='1')
6 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='0')
filter("T"."CENTER_ID"='0')
8 - access("BS"."SEX"=TO_NUMBER(:B1))
10 - access("PP"."PERS_TYPE"=TO_NUMBER(:B1) AND "PP"."CENTER_ID"=:B2)
13 - access("A"."INDI_ID"=:B1)
15 - access("T"."INDI_ID"=:B1)
17 - access("T"."CENTER_ID"=NVL(:B1,:B2) AND "T"."ICD"=:B3)
19 - access("T"."SERIAL_APPLY"=:B1)
21 - access("Q"."CENTER_ID"=NVL(:B1,:B2) AND "Q"."ICD"=:B3)
23 - access("TREATMENT_TYPE"=:B1 AND "CENTER_ID"=:B2)
25 - filter("SS"."VALID_FLAG"='1')
26 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
filter("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003')
28 - filter("SS"."VALID_FLAG"='1')
29 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
filter("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003')
34 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
36 - access("B"."CENTER_ID"="H"."CENTER_ID"(+))
38 - access("B"."CORP_ID"="STREET"."CORP_ID"(+))
40 - access("CORP"."AREA_CODE"="ST"."QYBM")
42 - filter("CORP"."AREA_CODE" IS NOT NULL)
43 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
45 - filter("B"."VALID_FLAG"='1')
54 - access("B"."HOSPITAL_ID"||"B"."SERIAL_NO"="HOSPITAL_ID"||"SERIAL_NO")
55 - filter("BIZ_TYPE"<>'12' OR "BIZ_FLAG"=18 AND "BIZ_TYPE"='12')
57 - filter("OUTPAY_BILL_NO"=23)
58 - access("MONTH_DECL_SN"="RELA_DECL_SN")
59 - filter("B"."VALID_FLAG"='1')
60 - filter("C"."VALID_FLAG"='1')
61 - access("B"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "B"."SERIAL_NO"="C"."SERIAL_NO")
62 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
63 - access("B"."INDI_ID"="J"."INDI_ID")
64 - access("B"."HOSPITAL_ID"="T"."HOSPITAL_ID" AND "B"."SERIAL_NO"="T"."SERIAL_NO")
65 - access("B"."INDI_ID"="J"."INDI_ID")
-------------------------------------------------------------------------------
task_name
---------
TASK_39762
err
---------
0
err
---------
0
接受调整优化生成的sql profile
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_39762',replace => TRUE);
PL/SQL procedure successfully completed
SQL> select * from dba_sql_profiles where sql_text like 'select rowno, hos_serial, reg_flag,%';
NAME CATEGORY SIGNATURE SQL_TEXT CREATED LAST_MODIFIED DESCRIPTION TYPE STATUS FORCE_MATCHING
------------------------------ ---------- ---------- ------------------------------------- ----------- ------------- ------------- --------- -------- --------------
SYS_SQLPROF_0154fb8617518000 DEFAULT 9.22119799 select rowno, hos_serial, reg_flag, 2016/4/5 16: 2016/4/5 16:31 MANUAL ENABLED NO
重新执行该SQL需要几分钟才能执行完成
查看其执行计划
SQL> set long 900
SQL> set linesize 900
SQL> select * from table(dbms_xplan.display_cursor('010tbjyy9cztf',null,'ALL ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 010tbjyy9cztf, child number 0
-------------------------------------
select rowno, hos_serial, reg_flag, nvl((SELECT distinct t.policy_value FROM fc_biz_policy t WHERE t.policy_code =
'can_clinic_flag' and t.valid_flag = '1' and t.center_id = '430701'), (SELECT distinct t.policy_value
FROM fc_biz_policy t WHERE t.policy_code = 'can_clinic_flag' and t.valid_flag = '1'
and t.center_id = '0')) AS can_clinic_flag, decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发', 'D', '危重', '') DISEASE_TYPE,
hospital_id, hospital_name, serial_no, biz_type, case_id, biz_stat, name, sex, pers_type, begin_date, end_date, fin_date, finish_date,
reg_date, in_days, indi_id, insr_code, fin_staff, fin_man, corp_id, idcard, district_code, office_grade,center_id, (select
t.special_code from bs_corp_pres t where t.indi_id = w.indi_id) as special_code, corp
Plan hash value: 3772053484
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 31 | | 2 (50)| 00:00:01 | 1307K| 1307K| |
|* 2 | TABLE ACCESS BY INDEX ROWID | FC_BIZ_POLICY | 1 | 31 | | 1 (0)| 00:00:01 | | | |
|* 3 | INDEX RANGE SCAN | PK_FC_BIZ_POLICY | 1 | | | 1 (0)| 00:00:01 | | | |
| 4 | HASH UNIQUE | | 1 | 31 | | 2 (50)| 00:00:01 | 1307K| 1307K| |
|* 5 | TABLE ACCESS BY INDEX ROWID | FC_BIZ_POLICY | 1 | 31 | | 1 (0)| 00:00:01 | | | |
|* 6 | INDEX RANGE SCAN | PK_FC_BIZ_POLICY | 1 | | | 1 (0)| 00:00:01 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | BS_SEX | 1 | 6 | | 1 (0)| 00:00:01 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_BS_SEX | 1 | | | 1 (0)| 00:00:01 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | BS_PERSON_TYPE | 1 | 18 | | 1 (0)| 00:00:01 | | | |
|* 10 | INDEX UNIQUE SCAN | PK_BS_PERSON_TYPE | 1 | | | 1 (0)| 00:00:01 | | | |
| 11 | SORT AGGREGATE | | 1 | 18 | | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | BS_INSURED | 1 | 18 | | 1 (0)| 00:00:01 | | | |
|* 13 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | | | 1 (0)| 00:00:01 | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | BS_CORP_PRES | 1 | 8 | | 1 (0)| 00:00:01 | | | |
|* 15 | INDEX RANGE SCAN | INDEX_BS_CORP_PRES_INDI | 1 | | | 1 (0)| 00:00:01 | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | | 1 (0)| 00:00:01 | | | |
|* 17 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | | 1 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | MT_APPLY | 1 | 9 | | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | PK_MT_APPLY | 1 | | | 1 (0)| 00:00:01 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | | 1 (0)| 00:00:01 | | | |
|* 21 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | | 1 (0)| 00:00:01 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | BS_TREAT_TYPE | 1 | 23 | | 1 (0)| 00:00:01 | | | |
|* 23 | INDEX UNIQUE SCAN | PK_BS_TREAT_TYPE | 1 | | | 1 (0)| 00:00:01 | | | |
| 24 | SORT AGGREGATE | | 1 | 34 | | | | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | MT_FEE_STAT_FIN | 1 | 34 | | 1 (0)| 00:00:01 | | | |
|* 26 | INDEX RANGE SCAN | PK_MT_FEE_STAT_FIN | 1 | | | 1 (0)| 00:00:01 | | | |
| 27 | SORT AGGREGATE | | 1 | 31 | | | | | | |
|* 28 | TABLE ACCESS BY INDEX ROWID | MT_FEE_STAT_FIN | 1 | 31 | | 1 (0)| 00:00:01 | | | |
|* 29 | INDEX RANGE SCAN | PK_MT_FEE_STAT_FIN | 1 | | | 1 (0)| 00:00:01 | | | |
| 30 | VIEW | | 20043 | 29M| | 309K (1)| 01:01:52 | | | |
| 31 | SORT ORDER BY | | 20043 | 16M| 34M| 309K (1)| 01:01:52 | 18M| 1609K| |
| 32 | COUNT | | | | | | | | | |
| 33 | NESTED LOOPS | | 20043 | 16M| | 305K (1)| 01:01:08 | | | |
|* 34 | HASH JOIN | | 20011 | 16M| | 305K (1)| 01:01:07 | 1000K| 1000K| |
| 35 | TABLE ACCESS FULL | BS_BIZTYPE | 97 | 1164 | | 3 (0)| 00:00:01 | | | |
|* 36 | HASH JOIN RIGHT OUTER | | 20011 | 16M| | 305K (1)| 01:01:07 | 1000K| 1000K| |
| 37 | TABLE ACCESS FULL | BS_CENTER | 12 | 168 | | 3 (0)| 00:00:01 | | | |
|* 38 | HASH JOIN RIGHT OUTER | | 20011 | 16M| | 305K (1)| 01:01:07 | 1023K| 1023K| |
| 39 | VIEW | | 1 | 12 | | 117 (2)| 00:00:02 | | | |
|* 40 | HASH JOIN | | 1 | 18 | | 117 (2)| 00:00:02 | 1133K| 1133K| 1/0/0|
| 41 | TABLE ACCESS FULL | BS_COUNTRY_STREET | 1 | 7 | | 2 (0)| 00:00:01 | | | |
|* 42 | TABLE ACCESS FULL | BS_CORP | 9523 | 102K| | 114 (1)| 00:00:02 | | | |
|* 43 | HASH JOIN | | 20011 | 15M| | 305K (1)| 01:01:06 | 876K| 876K| |
| 44 | INDEX FULL SCAN | IDX_BS_HOSPITAL_NAME | 1325 | 43725 | | 2 (0)| 00:00:01 | | | |
|* 45 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 225 | | 1 (0)| 00:00:01 | | | |
| 46 | NESTED LOOPS | | 25021 | 19M| | 305K (1)| 01:01:06 | | | |
| 47 | VIEW | | 25349 | 13M| | 295K (1)| 00:59:04 | | | |
| 48 | HASH GROUP BY | | 25349 | 1460K| | 295K (1)| 00:59:04 | 5134K| 1981K| |
| 49 | VIEW | | 25349 | 1460K| | 295K (1)| 00:59:04 | | | |
| 50 | HASH UNIQUE | | 25349 | 4876K| 10M| 295K (1)| 00:59:04 | 32M| 3975K| |
| 51 | NESTED LOOPS | | 25349 | 4876K| | 294K (1)| 00:58:51 | | | |
| 52 | NESTED LOOPS | | 25309 | 4424K| | 294K (1)| 00:58:51 | | | |
| 53 | NESTED LOOPS | | 25309 | 3880K| | 294K (1)| 00:58:51 | | | |
|* 54 | HASH JOIN | | 25502 | 2365K| 189M| 289K (1)| 00:57:50 | 1314K| 1168K| 1/0/0|
|* 55 | TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ | 205 | 6560 | | 9 (0)| 00:00:01 | | | |
| 56 | NESTED LOOPS | | 3750K| 146M| | 1405 (2)| 00:00:17 | | | |
|* 57 | TABLE ACCESS FULL | PM_BILL | 31 | 279 | | 1119 (2)| 00:00:14 | | | |
|* 58 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_MONTH | 213 | | | 1 (0)| 00:00:01 | | | |
|* 59 | TABLE ACCESS FULL | MT_BIZ_FIN | 21M| 1131M| | 209K (2)| 00:41:54 | | | |
|* 60 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 62 | | 1 (0)| 00:00:01 | | | |
|* 61 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | | 1 (0)| 00:00:01 | | | |
|* 62 | INDEX UNIQUE SCAN | PK_BS_BIZTYPE | 1 | 22 | | 1 (0)| 00:00:01 | | | |
|* 63 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 18 | | 1 (0)| 00:00:01 | | | |
|* 64 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | | 1 (0)| 00:00:01 | | | |
|* 65 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | | 1 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2
2 - SEL$2 / T@SEL$2
3 - SEL$2 / T@SEL$2
4 - SEL$3
5 - SEL$3 / T@SEL$3
6 - SEL$3 / T@SEL$3
7 - SEL$7 / BS@SEL$7
8 - SEL$7 / BS@SEL$7
9 - SEL$8 / PP@SEL$8
10 - SEL$8 / PP@SEL$8
11 - SEL$15
12 - SEL$15 / A@SEL$15
13 - SEL$15 / A@SEL$15
14 - SEL$4 / T@SEL$4
15 - SEL$4 / T@SEL$4
16 - SEL$9 / T@SEL$9
17 - SEL$9 / T@SEL$9
18 - SEL$11 / T@SEL$11
19 - SEL$11 / T@SEL$11
20 - SEL$10 / Q@SEL$10
21 - SEL$10 / Q@SEL$10
22 - SEL$5 / BS_TREAT_TYPE@SEL$5
23 - SEL$5 / BS_TREAT_TYPE@SEL$5
24 - SEL$16
25 - SEL$16 / SS@SEL$16
26 - SEL$16 / SS@SEL$16
27 - SEL$17
28 - SEL$17 / SS@SEL$17
29 - SEL$17 / SS@SEL$17
30 - SEL$6 / W@SEL$1
31 - SEL$6
35 - SEL$6 / G@SEL$6
37 - SEL$6 / H@SEL$6
39 - SEL$8F7BCF6F / STREET@SEL$6
40 - SEL$8F7BCF6F
41 - SEL$8F7BCF6F / ST@SEL$12
42 - SEL$8F7BCF6F / CORP@SEL$12
44 - SEL$6 / A@SEL$6
45 - SEL$6 / B@SEL$6
47 - SEL$6C11BF2C / T@SEL$6
48 - SEL$6C11BF2C
49 - SEL$93984FCC / $vm_view@SEL$6C11BF2C
50 - SEL$93984FCC
55 - SEL$93984FCC / PM_ACCOUNT_BIZ@SEL$18
57 - SEL$93984FCC / PM_BILL@SEL$19
58 - SEL$93984FCC / PM_ACCOUNT_BIZ@SEL$18
59 - SEL$93984FCC / B@SEL$14
60 - SEL$93984FCC / C@SEL$14
61 - SEL$93984FCC / C@SEL$14
62 - SEL$93984FCC / G@SEL$14
63 - SEL$93984FCC / J@SEL$14
64 - SEL$6 / B@SEL$6
65 - SEL$6 / J@SEL$6
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."VALID_FLAG"='1')
3 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='430701')
filter("T"."CENTER_ID"='430701')
5 - filter("T"."VALID_FLAG"='1')
6 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='0')
filter("T"."CENTER_ID"='0')
8 - access("BS"."SEX"=TO_NUMBER(:B1))
10 - access("PP"."PERS_TYPE"=TO_NUMBER(:B1) AND "PP"."CENTER_ID"=:B2)
13 - access("A"."INDI_ID"=:B1)
15 - access("T"."INDI_ID"=:B1)
17 - access("T"."CENTER_ID"=NVL(:B1,:B2) AND "T"."ICD"=:B3)
19 - access("T"."SERIAL_APPLY"=:B1)
21 - access("Q"."CENTER_ID"=NVL(:B1,:B2) AND "Q"."ICD"=:B3)
23 - access("TREATMENT_TYPE"=:B1 AND "CENTER_ID"=:B2)
25 - filter("SS"."VALID_FLAG"='1')
26 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
filter(("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003'))
28 - filter("SS"."VALID_FLAG"='1')
29 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
filter(("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003'))
34 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
36 - access("B"."CENTER_ID"="H"."CENTER_ID")
38 - access("B"."CORP_ID"="STREET"."CORP_ID")
40 - access("CORP"."AREA_CODE"="ST"."QYBM")
42 - filter("CORP"."AREA_CODE" IS NOT NULL)
43 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
45 - filter("B"."VALID_FLAG"='1')
54 - access("B"."HOSPITAL_ID"||"B"."SERIAL_NO"="HOSPITAL_ID"||"SERIAL_NO")
55 - filter(("BIZ_TYPE"<>'12' OR ("BIZ_FLAG"=18 AND "BIZ_TYPE"='12')))
57 - filter("OUTPAY_BILL_NO"=23)
58 - access("MONTH_DECL_SN"="RELA_DECL_SN")
59 - filter("B"."VALID_FLAG"='1')
60 - filter("C"."VALID_FLAG"='1')
61 - access("B"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "B"."SERIAL_NO"="C"."SERIAL_NO")
62 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
63 - access("B"."INDI_ID"="J"."INDI_ID")
64 - access("B"."HOSPITAL_ID"="T"."HOSPITAL_ID" AND "B"."SERIAL_NO"="T"."SERIAL_NO")
65 - access("B"."INDI_ID"="J"."INDI_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."POLICY_VALUE"[VARCHAR2,200]
2 - "T"."POLICY_VALUE"[VARCHAR2,200]
3 - "T".ROWID[ROWID,10]
4 - "T"."POLICY_VALUE"[VARCHAR2,200]
5 - "T"."POLICY_VALUE"[VARCHAR2,200]
6 - "T".ROWID[ROWID,10]
7 - "BS".ROWID[ROWID,10], "BS"."SEX_NAME"[VARCHAR2,5]
8 - "BS".ROWID[ROWID,10]
9 - "PP".ROWID[ROWID,10], "PP"."PERS_NAME"[VARCHAR2,20]
10 - "PP".ROWID[ROWID,10]
11 - (#keys=0) MAX("INSR_CODE")[30]
12 - "INSR_CODE"[VARCHAR2,30]
13 - "A".ROWID[ROWID,10]
14 - "T".ROWID[ROWID,10], "T"."SPECIAL_CODE"[NUMBER,22]
15 - "T".ROWID[ROWID,10]
16 - "T".ROWID[ROWID,10], "T"."DISEASE"[VARCHAR2,100]
17 - "T".ROWID[ROWID,10]
18 - "T".ROWID[ROWID,10], "T"."APPLY_CONTENT"[VARCHAR2,3]
19 - "T".ROWID[ROWID,10]
20 - "Q".ROWID[ROWID,10], "Q"."DISEASE"[VARCHAR2,100]
21 - "Q".ROWID[ROWID,10]
22 - "BS_TREAT_TYPE".ROWID[ROWID,10], "TREATMENT_NAME"[VARCHAR2,50]
23 - "BS_TREAT_TYPE".ROWID[ROWID,10]
24 - (#keys=0) SUM("SS"."PART_SELF_MONEY")[22], SUM("SS"."ALL_SELF_MONEY")[22]
25 - "SS"."PART_SELF_MONEY"[NUMBER,22], "SS"."ALL_SELF_MONEY"[NUMBER,22]
26 - "SS".ROWID[ROWID,10]
27 - (#keys=0) SUM("SS"."MONEY")[22]
28 - "SS"."MONEY"[NUMBER,22]
29 - "SS".ROWID[ROWID,10]
30 - "ROWNO"[NUMBER,22], "HOS_SERIAL"[VARCHAR2,30], "REG_FLAG"[CHARACTER,1], "DISEASE_TYPE"[CHARACTER,1], "HOSPITAL_ID"[VARCHAR2,20],
"HOSPITAL_NAME"[VARCHAR2,70], "SERIAL_NO"[VARCHAR2,16], "BIZ_TYPE"[VARCHAR2,2], "CASE_ID"[NUMBER,22], "BIZ_STAT"[VARCHAR2,2], "NAME"[VARCHAR2,20],
"SEX"[VARCHAR2,5], "PERS_TYPE"[VARCHAR2,20], "BEGIN_DATE"[VARCHAR2,10], "END_DATE"[VARCHAR2,10], "FIN_DATE"[VARCHAR2,19], "FINISH_DATE"[VARCHAR2,19],
"INDI_ID"[NUMBER,22], "INSR_CODE"[VARCHAR2,30], "FIN_STAFF"[VARCHAR2,10], "FIN_MAN"[VARCHAR2,30], "CORP_ID"[NUMBER,22], "IDCARD"[VARCHAR2,25],
"REG_DATE"[VARCHAR2,19], "IN_DAYS"[NUMBER,22], "DISTRICT_CODE"[VARCHAR2,6], "CENTER_ID"[VARCHAR2,10], "OFFICE_GRADE"[VARCHAR2,3],
"CORP_NAME"[VARCHAR2,70], "DISEASE"[VARCHAR2,100], "IN_AREA_NAME"[VARCHAR2,20], "IN_DEPT_NAME"[VARCHAR2,20], "IN_BED"[VARCHAR2,10],
"BED_TYPE"[CHARACTER,1], "PATIENT_ID"[VARCHAR2,20], "REMARK"[VARCHAR2,1000], "POS_CODE"[VARCHAR2,10], "REIMBURSE_FLAG"[CHARACTER,1],
"FIN_DISEASE"[VARCHAR2,100], "FOREGIFT"[NUMBER,1], "IC_NO"[VARCHAR2,25], "TREATMENT_TYPE"[VARCHAR2,3], "APPLY_CONTENT"[VARCHAR2,3],
"REG_MAN"[VARCHAR2,20], "W"."AREA_CODE"[VARCHAR2,10], "FEES"[NUMBER,22], "PAY_MONEY_JD"[NUMBER,22], "FUND_MONEY"[NUMBER,22], "FUND_301"[NUMBER,22],
"FUND_003"[NUMBER,22], "QFX"[NUMBER,22], "HOSP_ZF"[NUMBER,22], "CENTER_ZF"[NUMBER,22], "YW_FUND_MONEY"[NUMBER,22], "BC_FUND_MONEY"[NUMBER,22],
"PAY_MONEY_ALLSELF"[NUMBER,22], "PAY_MONEY_SELF"[NUMBER,22], "PAY_MONEY_E00SELF"[NUMBER,22], "PAY_MONEY_Z00SELF"[NUMBER,22],
"PAY_MONEY_S00SELF"[NUMBER,22], "PAY_MONEY_S01SELF"[NUMBER,22], "PAY_MONEY_C000SELF"[NUMBER,22], "PAY_MONEY_C001SELF"[NUMBER,22],
"PAY_MONEY_C004SELF"[NUMBER,22], "PAY_MONEY_C006SELF"[NUMBER,22], "PAY_MONEY_C007SELF"[NUMBER,22], "PAY_MONEY_C007003SELF"[NUMBER,22],
"DB_MONEY"[NUMBER,22], "YWSH_DB_MONEY"[NUMBER,1], "TC_MONEY"[NUMBER,22], "TC_MONEY_XE"[NUMBER,22], "HOSP_PAY"[NUMBER,22], "HOSP_PRISE"[NUMBER,22],
"JMTC_MONEY"[NUMBER,22], "YW_MONEY"[NUMBER,22], "JMYW_MONEY"[NUMBER,22], "ACCT_MONEY"[NUMBER,22], "LX_MONEY"[NUMBER,22], "SY_MONEY"[NUMBER,22],
"OFFI_MONEY"[NUMBER,22], "ZHAOGU_PAY"[NUMBER,22], "PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
"MEDI_ZFY"[NUMBER,22], "REG_INFO"[VARCHAR2,10], "DB_MONEY_JUMIN"[NUMBER,22]
31 - (#keys=1) ROWNUM[22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."REG_FLAG"[CHARACTER,1],
"B"."DISEASE_TYPE"[CHARACTER,1], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."BIZ_TYPE"[VARCHAR2,2], "B"."CASE_ID"[NUMBER,22], "G"."BIZ_STAT"[VARCHAR2,2], "B"."NAME"[VARCHAR2,20], [5], [20],
TO_CHAR(INTERNAL_FUNCTION("B"."BEGIN_DATE"),'yyyy-mm-dd')[10], TO_CHAR(INTERNAL_FUNCTION("B"."END_DATE"),'yyyy-mm-dd')[10],
TO_CHAR(INTERNAL_FUNCTION("B"."FIN_DATE"),'yyyy-mm-dd hh24:mi:ss')[19], TO_CHAR(INTERNAL_FUNCTION("B"."FIN_DATE"),'yyyy-mm-dd hh24:mi:ss')[19],
"B"."INDI_ID"[NUMBER,22], "T"."INSR_CODE"[VARCHAR2,30], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CORP_ID"[NUMBER,22],
"B"."IDCARD"[VARCHAR2,25], TO_CHAR(INTERNAL_FUNCTION("B"."REG_DATE"),'yyyy-mm-dd hh24:mi:ss')[19], NVL("B"."IN_DAYS",0)[22],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."CENTER_ID"[VARCHAR2,10], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."CORP_NAME"[VARCHAR2,70],
"B"."IN_DISEASE"[VARCHAR2,20], [100], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
','')[1000], "B"."POS_CODE"[VARCHAR2,10],PATIENT_ID"[VARCHAR2,20], TRANSLATE("B"."REMARK",'
"B"."REIMBURSE_FLAG"[CHARACTER,1], [100], 0[1], "B"."IC_NO"[VARCHAR2,25], "B"."TREATMENT_TYPE"[VARCHAR2,3], [3], "B"."REG_MAN"[VARCHAR2,20],
"STREET"."QYBM"[VARCHAR2,10], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
"T"."FUND_003"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
"T"."BC_FUND_MONEY"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
"T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
"T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
"T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"-"T"."YWSH_DB_MONEY"[22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22],
"T"."TC_MONEY_XE"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22],
"T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22],
"T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
"B"."REG_INFO"[VARCHAR2,10]
32 - "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
"B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
"A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
"T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
"T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
"T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
"T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
"T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
"T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
"T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
"T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
"MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
"B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
"B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
"B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], ROWNUM[4]
33 - "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
"B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
"A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
"T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
"T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
"T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
"T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
"T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
"T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
"T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
"T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
"MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
"B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
"B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
"B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30]
34 - (#keys=2) "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
"B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
"A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
"T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
"T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
"T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
"T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
"T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
"T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
"T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
"T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
"MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
"B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
"B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
"B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30]
35 - "G"."CENTER_ID"[VARCHAR2,10], "G"."BIZ_TYPE"[CHARACTER,2], "G"."BIZ_STAT"[VARCHAR2,2]
36 - (#keys=1) "H"."CENTER_ID"[VARCHAR2,10], "B"."CENTER_ID"[VARCHAR2,10], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22],
"STREET"."QYBM"[VARCHAR2,10], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
"T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
"T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
"T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
"T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
"T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
"T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
"T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
"T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
"MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20],
"B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30],
"B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20],
"B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20],
"B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22],
"B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500],
"B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3]
37 - "H"."CENTER_ID"[VARCHAR2,10], "H"."CATALOG_CENTER"[VARCHAR2,10]
38 - (#keys=1) "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70],
"T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
"T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22],
"T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
"T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
"T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
"T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22],
"T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22],
"T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22],
"T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
"T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
"B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
"B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
"B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3]
39 - "STREET"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10]
40 - (#keys=1) "ST"."QYBM"[VARCHAR2,10], "CORP"."CORP_ID"[NUMBER,22]
41 - "ST"."QYBM"[VARCHAR2,10]
42 - "CORP"."CORP_ID"[NUMBER,22], "CORP"."AREA_CODE"[VARCHAR2,20]
43 - (#keys=1) "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
"T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
"T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
"T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
"T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
"T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
"T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
"T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
"T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
"MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20],
"B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."CORP_ID"[NUMBER,22],
"B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20],
"B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20],
"B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22],
"B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500],
"B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3],
"B"."HOS_SERIAL"[VARCHAR2,30]
44 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70]
45 - "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."CORP_ID"[NUMBER,22], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
"B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
"B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
"B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3],
"B"."HOS_SERIAL"[VARCHAR2,30], "B"."DISEASE_TYPE"[CHARACTER,1]
46 - "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
"T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22],
"T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
"T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
"T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
"T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22],
"T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22],
"T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22],
"T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
"T"."DB_MONEY_JUMIN"[NUMBER,22], "SYS_ALIAS_10".ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]
47 - "T"."HOSPITAL_ID"[VARCHAR2,20], "T"."SERIAL_NO"[VARCHAR2,16], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
"T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
"T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
"T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
"T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
"T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
"T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
"T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
"T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
"MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22]
48 - "$vm_col_8"[VARCHAR2,20], "$vm_col_9"[VARCHAR2,16], "$vm_col_7"[NUMBER,22], SUM(DECODE("$vm_col_5",'803',"$vm_col_4",0))[22], SUM(CASE WHEN
(("$vm_col_3"='C000' OR "$vm_col_3"='C001' OR "$vm_col_3"='C002' OR "$vm_col_3"='C003' OR "$vm_col_3"='C004') AND ("$vm_col_5"='999' OR
"$vm_col_5"='003')) THEN "$vm_col_4" ELSE 0 END )[22], SUM(DECODE("$vm_col_5",'999',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_5",'401',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'301',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'511',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_5",'202',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'003',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'802',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_5",'901',"$vm_col_4",'802',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'801',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_5",'001',"$vm_col_4",'801',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'996',NVL("$vm_col_4",0),0))[22],
SUM(DECODE("$vm_col_5",'201',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C007003',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_3"||"$vm_col_5",'C007999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C006003',"$vm_col_4",'C006999',"$vm_col_4",'C0
07003',"$vm_col_4",'C007999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C004003',"$vm_col_4",'C004999',"$vm_col_4",'C005003',"$vm_col_4
",'C005999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C001003',"$vm_col_4",'C001999',"$vm_col_4",'C002003',"$vm_col_4",'C002999',"$vm_
col_4",'C003003',"$vm_col_4",'C003999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C000003',"$vm_col_4",'C000999',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_3"||"$vm_col_5",'S02003',"$vm_col_4",'S02999',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_3"||"$vm_col_5",'S01003',"$vm_col_4",'S01999',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_3"||"$vm_col_5",'S00003',"$vm_col_4",'S00999',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_3"||"$vm_col_5",'Z00003',"$vm_col_4",'Z00999',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_3"||"$vm_col_5",'E00003',"$vm_col_4",'E00999',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_2"||"$vm_col_5",'102003',"$vm_col_4",'102999',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_2"||"$vm_col_5",'101003',"$vm_col_4",'101999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'998',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_5",'996',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3",'S01',"$vm_col_4",'S02',"$vm_col_4",0))[22],
SUM(DECODE("$vm_col_5",'306',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'001',"$vm_col_4",'511',"$vm_col_4",'202',"$vm_col_4",'801',"$vm_col_4",0))[2
2], SUM(DECODE("$vm_col_3",'C000',"$vm_col_4",'C001',"$vm_col_4",'C002',"$vm_col_4",'C003',"$vm_col_4",'C004',"$vm_col_4",'C005',"$vm_col_4",'C006',"$
vm_col_4",'C007',"$vm_col_4",0))[22], SUM("$vm_col_4")[22]
49 - "$vm_col_8"[VARCHAR2,20], "$vm_col_9"[VARCHAR2,16], "$vm_col_7"[NUMBER,22], "$vm_col_2"[VARCHAR2,3], "$vm_col_3"[VARCHAR2,20],
"$vm_col_4"[NUMBER,22], "$vm_col_5"[VARCHAR2,3]
50 - "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."INDI_ID"[NUMBER,22], ROWID[ROWID,10], ROWID[ROWID,10], ROWID[ROWID,10],
ROWID[ROWID,10], "HOSPITAL_ID"||"SERIAL_NO"[36], "C"."LABEL_FLAG"[VARCHAR2,3], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."REAL_PAY"[NUMBER,22],
"C"."FUND_ID"[VARCHAR2,3], "FINISH_FLAG"[CHARACTER,1]
51 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3],
"C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3], ROWID[ROWID,10], ROWID[ROWID,10]
52 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3],
"C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3], ROWID[ROWID,10]
53 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10], ROWID[ROWID,10],
"C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3], "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3]
54 - (#keys=1) "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10]
55 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16]
56 - "PM_ACCOUNT_BIZ".ROWID[ROWID,10]
57 - "RELA_DECL_SN"[NUMBER,22]
58 - "PM_ACCOUNT_BIZ".ROWID[ROWID,10]
59 - ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22],
"FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10]
60 - ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3], "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3]
61 - ROWID[ROWID,10]
62 - ROWID[ROWID,10]
63 - ROWID[ROWID,10]
64 - "SYS_ALIAS_10".ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]
Note
-----
- SQL profile "SYS_SQLPROF_0154fb8617518000" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
456 rows selected.
使用sql profile后执行时间是几分种，执行计划如上图所示，消耗时间的操作是对表mt_biz_fin执行全表扫描，该表的记录数是21M，访问该表的谓词条件用到了该表的hostpital_id与serial_no，而它们是该表的复合主键的前导列，而谓词条件写成了
b.hospital_id||b.serial_no in() 这就没有办法用到主键索引，应该用(b.hosptial_id,b.serial_no) in()。删除sql profile:
SQL> execute dbms_sqltune.drop_sql_profile(name => 'SYS_SQLPROF_0154fb8617518000'); PL/SQL procedure successfully completed SQL> select * from dba_sql_profiles where sql_text like 'select rowno, hos_serial, reg_flag,%'; NAME CATEGORY SIGNATURE SQL_TEXT CREATED LAST_MODIFIED DESCRIPTION TYPE STATUS FORCE_MATCHING ------------------------------ ------------------------------ ---------- ----------------- ----------- ------------- ------------- --------- -------- --------------
将谓词条件b.hospital_id||b.serial_no in()修改为（b.hosptial_id,b.serial_no) in()。修改后的sql如下所示:
select /*+ jy_20160405_01 */ rowno, hos_serial, reg_flag,
nvl((SELECT distinct t.policy_value
FROM fc_biz_policy t
WHERE t.policy_code = 'can_clinic_flag'
and t.valid_flag = '1'
and t.center_id = '430701'), (SELECT distinct t.policy_value
FROM fc_biz_policy t
WHERE t.policy_code =
'can_clinic_flag'
and t.valid_flag = '1'
and t.center_id = '0')) AS can_clinic_flag,
decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发', 'D', '危重', '') DISEASE_TYPE,
hospital_id, hospital_name, serial_no, biz_type, case_id, biz_stat,
name, sex, pers_type, begin_date, end_date, fin_date, finish_date,
reg_date, in_days, indi_id, insr_code, fin_staff, fin_man, corp_id,
idcard, district_code, office_grade,center_id,
(select t.special_code
from bs_corp_pres t
where t.indi_id = w.indi_id) as special_code, corp_name, disease,
in_area_name, in_dept_name,
decode(apply_content, '126', '转外住院', '127', '转外复查', '普通住院') as apply_content,
in_bed, bed_type, patient_id, remark, pos_code, reimburse_flag,
fin_disease, ic_no, treatment_type,
decode(reg_info || treatment_type, 'WD3120', '普通住院(转外住院)', 'WD2120', '普通住院(异地住院)', '1120', '普通住院(转诊转院)', 'F120', '普通住院(非首诊就诊)', 'WD1120', '普通住院(异地安置)', 'C120', '普通住院(首诊就诊)', (select treatment_name
from bs_treat_type
where treatment_type =
w.treatment_type
and center_id =
w.center_id)) as treatment_name,
nvl(pay_money_jd, 0) as pay_money_jd,
nvl(pay_money_xzf, 0) as pay_money_xzf, nvl(fees, 0) as fees,
foregift, nvl(pay_money_allself, 0) as pay_money_allself,
nvl(pay_money_self, 0) as pay_money_self,
nvl(pay_money_E00self, 0) as pay_money_E00self,
nvl(pay_money_Z00self, 0) as pay_money_Z00self,
nvl(pay_money_S00self, 0) as pay_money_S00self,
nvl(pay_money_S01self, 0) as pay_money_S01self,
nvl(pay_money_S01self, 0) as pay_money_S01self,
nvl(pay_money_C000self, 0) as pay_money_C000self,
nvl(pay_money_C001self, 0) as pay_money_C001self,
nvl(pay_money_C004self, 0) as pay_money_C004self,
nvl(pay_money_C006self, 0) as pay_money_C006self,
nvl(pay_money_C007self, 0) as pay_money_C007self,
nvl(pay_money_C007003self, 0) as pay_money_C007003self,
nvl(tc_money, 0) as tc_money, nvl(tc_money_xe, 0) as tc_money_xe,
nvl(hosp_pay, 0) as hosp_pay, nvl(hosp_prise, 0) as hosp_prise,
nvl(jmtc_money, 0) as jmtc_money, nvl(yw_money, 0) as yw_money,
nvl(jmyw_money, 0) as jmyw_money, nvl(acct_money, 0) as acct_money,
nvl(lx_money, 0) as lx_money, nvl(sy_money, 0) as sy_money,
nvl(fund_money, 0) as fund_money, nvl(fund_301,0) fund_301 ,nvl(fund_003,0) fund_003 ,nvl(qfx, 0) as qfx,
nvl(hosp_zf, 0) as hosp_zf, nvl(center_zf, 0) as center_zf,
nvl(yw_fund_money, 0) as yw_fund_money,
nvl(bc_fund_money, 0) as bc_fund_money, nvl(db_money, 0) as db_money,
nvl(YWSH_DB_MONEY, 0) as YWSH_DB_MONEY,
nvl(offi_money, 0) as offi_money, reg_man,
nvl(zhaogu_pay, 0) as zhaogu_pay, w.area_code, PAY_MONEY_DNZF,
medi_pay, medi_zfy, nvl(DB_MONEY_JUMIN, 0) as db_money_jumin
from (select rownum rowno, b.hos_serial, b.reg_flag, b.disease_type,
a.hospital_id, a.hospital_name, b.serial_no, b.biz_type,
b.case_id, g.biz_stat, b.name, (select bs.sex_name from bs_sex bs where bs.sex =b.sex) sex,
(select pp.pers_name from bs_person_type pp where pp.pers_type = b.pers_type_detail and pp.center_id = b.center_id) as pers_type,
to_char(b.begin_date, 'yyyy-mm-dd') begin_date,
to_char(b.end_date, 'yyyy-mm-dd') end_date,
to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss') fin_date,
to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss') finish_date,
b.indi_id, t.insr_code, b.fin_staff, b.fin_man, b.corp_id,
b.idcard,
to_char(b.reg_date, 'yyyy-mm-dd hh24:mi:ss') reg_date,
nvl(b.in_days, 0) in_days, b.district_code, b.center_id,
b.office_grade, b.corp_name, b.in_disease,
(select t.disease
from bs_disease t
where t.center_id = nvl(h.catalog_center, h.center_id)
and b.in_disease = t.icd) as disease, b.in_area_name,
b.in_dept_name, b.in_bed, b.bed_type, b.patient_id,
translate(b.remark, chr(13), '') remark, b.pos_code,
b.reimburse_flag,
(select q.disease
from bs_disease q
where q.center_id = nvl(h.catalog_center, h.center_id)
and b.fin_disease = q.icd) as fin_disease, 0 as foregift,
b.ic_no, b.treatment_type,
(select t.apply_content
from mt_apply t
where t.serial_apply = b.serial_apply) as apply_content,
b.reg_man, street.QYBM as area_code, T.FEES, T.PAY_MONEY_JD,
T.FUND_MONEY, T.FUND_301, T.FUND_003,T.QFX, T.HOSP_ZF, T.CENTER_ZF, T.YW_FUND_MONEY,
T.BC_FUND_MONEY, T.PAY_MONEY_ALLSELF, T.PAY_MONEY_SELF,
T.PAY_MONEY_E00SELF, T.PAY_MONEY_Z00SELF, T.PAY_MONEY_S00SELF,
T.PAY_MONEY_S01SELF, T.PAY_MONEY_S02SELF, T.PAY_MONEY_C000SELF,
T.PAY_MONEY_C001SELF, T.PAY_MONEY_C004SELF,
T.PAY_MONEY_C006SELF, T.PAY_MONEY_C007SELF,
T.PAY_MONEY_C007003SELF,
(T.DB_MONEY - t.YWSH_DB_MONEY) as DB_MONEY, t.YWSH_DB_MONEY,
T.TC_MONEY, T.TC_MONEY_XE, t.hosp_pay, t.hosp_prise,
T.JMTC_MONEY, T.YW_MONEY, T.JMYW_MONEY, T.ACCT_MONEY,
T.LX_MONEY, T.SY_MONEY, T.OFFI_MONEY, T.ZHAOGU_PAY,
T.PAY_MONEY_XZF, PAY_MONEY_DNZF, medi_pay, medi_zfy,
b.REG_INFO, T.DB_MONEY_JUMIN as DB_MONEY_JUMIN
from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h,
bs_insured j,
(select corp.CORP_ID, corp.AREA_CODE, st.QYBM
from bs_corp corp
inner join bs_country_street st
on corp.AREA_CODE = st.QYBM) street,
(SELECT B.hospital_id, B.serial_no, B.indi_id,
(select max(insr_code)
from bs_insured a
where a.indi_id = B.indi_id) insr_code,
SUM(c.real_pay) AS FEES,
SUM(DECODE(c.POLICY_ITEM_CODE, 'C000', c.REAL_PAY, 'C001', c.REAL_PAY, 'C002', c.REAL_PAY, 'C003', c.REAL_PAY, 'C004', c.REAL_PAY, 'C005', c.REAL_PAY, 'C006', c.REAL_PAY, 'C007', c.REAL_PAY, 0)) AS PAY_MONEY_JD,
SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '511', c.REAL_PAY, '202', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS FUND_MONEY,
SUM(DECODE(c.FUND_ID, '301', c.REAL_PAY, 0)) AS FUND_301,
SUM(DECODE(c.FUND_ID, '003', c.REAL_PAY, 0)) AS FUND_003,
SUM(DECODE(c.FUND_ID, '901', c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_FUND_MONEY,
SUM(DECODE(c.FUND_ID, '306', c.REAL_PAY, 0)) AS BC_FUND_MONEY,
SUM(DECODE(c.POLICY_ITEM_CODE, 'S01', c.REAL_PAY, 'S02', c.REAL_PAY, 0)) AS QFX,
SUM(DECODE(c.FUND_ID, '996', c.REAL_PAY, 0)) AS HOSP_ZF,
SUM(DECODE(c.FUND_ID, '998', c.REAL_PAY, 0)) AS CENTER_ZF,
SUM(DECODE(c.label_flag || c.FUND_ID, '101003', c.REAL_PAY, '101999', c.REAL_PAY, 0)) AS PAY_MONEY_ALLSELF,
SUM(DECODE(c.label_flag || c.FUND_ID, '102003', c.REAL_PAY, '102999', c.REAL_PAY, 0)) AS PAY_MONEY_SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'E00003', c.REAL_PAY, 'E00999', c.REAL_PAY, 0)) AS PAY_MONEY_E00SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'Z00003', c.REAL_PAY, 'Z00999', c.REAL_PAY, 0)) AS PAY_MONEY_Z00SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S00003', c.REAL_PAY, 'S00999', c.REAL_PAY, 0)) AS PAY_MONEY_S00SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S01003', c.REAL_PAY, 'S01999', c.REAL_PAY, 0)) AS PAY_MONEY_S01SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S02003', c.REAL_PAY, 'S02999', c.REAL_PAY, 0)) AS PAY_MONEY_S02SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C000003', c.REAL_PAY, 'C000999', c.REAL_PAY, 0)) AS PAY_MONEY_C000SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C001003', c.REAL_PAY, 'C001999', c.REAL_PAY, 'C002003', c.REAL_PAY, 'C002999', c.REAL_PAY, 'C003003', c.REAL_PAY, 'C003999', c.REAL_PAY, 0)) AS PAY_MONEY_C001SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C004003', c.REAL_PAY, 'C004999', c.REAL_PAY, 'C005003', c.REAL_PAY, 'C005999', c.REAL_PAY, 0)) AS PAY_MONEY_C004SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C006003', c.REAL_PAY, 'C006999', c.REAL_PAY, 'C007003', c.REAL_PAY, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C006SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C007SELF,
SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C007003', c.REAL_PAY, 0)) AS PAY_MONEY_C007003SELF,
SUM(DECODE(c.FUND_ID, '201', c.REAL_PAY, 0)) AS DB_MONEY,
0 AS YWSH_DB_MONEY,
SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY,
case
when nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0) >= 0 then
nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0)
else
0
end hosp_pay,
case
when nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0) >= 0 then
0
else
-nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0)
end hosp_prise,
SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY_XE,
SUM(DECODE(c.FUND_ID, '801', c.REAL_PAY, 0)) AS JMTC_MONEY,
SUM(DECODE(c.FUND_ID, '901', c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_MONEY,
SUM(DECODE(c.FUND_ID, '802', c.REAL_PAY, 0)) AS JMYW_MONEY,
SUM(DECODE(c.FUND_ID, '003', c.REAL_PAY, 0)) AS ACCT_MONEY,
SUM(DECODE(c.FUND_ID, '202', c.REAL_PAY, 0)) AS LX_MONEY,
SUM(DECODE(c.FUND_ID, '511', c.REAL_PAY, 0)) AS SY_MONEY,
SUM(DECODE(c.FUND_ID, '301', c.REAL_PAY, 0)) AS OFFI_MONEY,
SUM(DECODE(c.FUND_ID, '401', c.REAL_PAY, 0)) AS ZHAOGU_PAY,
SUM(DECODE(c.FUND_ID, '999', c.REAL_PAY, 0)) AS PAY_MONEY_XZF,
SUM((CASE
WHEN C.POLICY_ITEM_CODE IN
('C000', 'C001', 'C002', 'C003', 'C004') AND
(C.fund_id = '999' OR C.fund_id = '003') THEN
C.REAL_PAY
ELSE
0
END)) AS PAY_MONEY_DNZF,
(select nvl(nvl(sum(ss.all_self_money), 0) +
nvl(sum(ss.part_self_money), 0), 0) as not_fund
from mt_fee_stat_fin ss
where ss.hospital_id = b.hospital_id
and ss.serial_no = b.serial_no
and ss.valid_flag = '1'
and ss.stat_type in ('001', '002', '003')) as medi_pay,
(select nvl(sum(ss.money), 0) as medi_zfy
from mt_fee_stat_fin ss
where ss.hospital_id = b.hospital_id
and ss.serial_no = b.serial_no
and ss.valid_flag = '1'
and ss.stat_type in ('001', '002', '003')) as medi_zfy,
SUM(DECODE(c.FUND_ID, '803', c.REAL_PAY, 0)) AS DB_MONEY_JUMIN
FROM MT_BIZ_FIN B, MT_PAY_RECORD_FIN C, BS_BIZTYPE G,
BS_INSURED J
WHERE B.HOSPITAL_ID = C.HOSPITAL_ID
AND B.SERIAL_NO = C.SERIAL_NO
AND B.CENTER_ID = G.CENTER_ID
AND B.BIZ_TYPE = G.BIZ_TYPE
AND B.VALID_FLAG = '1'
AND C.VALID_FLAG = '1'
and b.indi_id = j.indi_id
and (b.hospital_id,b.serial_no) in
(select hospital_id,serial_no
from pm_account_biz
where month_decl_sn in
(select rela_decl_sn
from pm_bill
where outpay_bill_no = '23') and ((biz_flag = '18' and biz_type = '12') or biz_type <>'12' ))
GROUP BY b.hospital_id, b.serial_no, b.indi_id) T
where a.hospital_id = b.hospital_id
and b.center_id = g.center_id
and b.biz_type = g.biz_type
and b.center_id = h.center_id(+)
and b.valid_flag = '1'
and b.indi_id = j.indi_id
and b.CORP_ID = street.CORP_ID(+)
AND B.HOSPITAL_ID = T.HOSPITAL_ID
AND B.SERIAL_NO = T.SERIAL_NO
order by rowno) w
修改后的sql执行时间在10秒以内:
查看其执行计划:
SQL> select * from table(dbms_xplan.display_cursor('3vgjr2kwtyncm',null,'ALL ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3vgjr2kwtyncm, child number 0
-------------------------------------
select /*+ jy_20160405_01 */ rowno, hos_serial, reg_flag, nvl((SELECT distinct t.policy_value FROM fc_biz_policy t
WHERE t.policy_code = 'can_clinic_flag' and t.valid_flag = '1' and t.center_id = '430701'), (SELECT distinct
t.policy_value FROM fc_biz_policy t WHERE t.policy_code = 'can_clinic_flag' and
t.valid_flag = '1' and t.center_id = '0')) AS can_clinic_flag, decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发', 'D',
'危重', '') DISEASE_TYPE, hospital_id, hospital_name, serial_no, biz_type, case_id, biz_stat, name, sex, pers_type, begin_date, end_date,
fin_date, finish_date, reg_date, in_days, indi_id, insr_code, fin_staff, fin_man, corp_id, idcard, district_code, office_grade,center_id,
(select t.special_code from bs_corp_pres t where t.indi_id = w.indi_id)
Plan hash value: 3988998535
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M | Max-Tmp |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 31 | 2 (50)| 00:00:01 | 1307K| 1307K| 1/0/0| |
|* 2 | TABLE ACCESS BY INDEX ROWID | FC_BIZ_POLICY | 1 | 31 | 1 (0)| 00:00:01 | | | | |
|* 3 | INDEX RANGE SCAN | PK_FC_BIZ_POLICY | 1 | | 1 (0)| 00:00:01 | | | | |
| 4 | HASH UNIQUE | | 1 | 31 | 2 (50)| 00:00:01 | 1594K| 1594K| 1/0/0| |
|* 5 | TABLE ACCESS BY INDEX ROWID | FC_BIZ_POLICY | 1 | 31 | 1 (0)| 00:00:01 | | | | |
|* 6 | INDEX RANGE SCAN | PK_FC_BIZ_POLICY | 1 | | 1 (0)| 00:00:01 | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | BS_SEX | 1 | 6 | 1 (0)| 00:00:01 | | | | |
|* 8 | INDEX UNIQUE SCAN | PK_BS_SEX | 1 | | 1 (0)| 00:00:01 | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | BS_PERSON_TYPE | 1 | 18 | 1 (0)| 00:00:01 | | | | |
|* 10 | INDEX UNIQUE SCAN | PK_BS_PERSON_TYPE | 1 | | 1 (0)| 00:00:01 | | | | |
| 11 | SORT AGGREGATE | | 1 | 18 | | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | BS_INSURED | 1 | 18 | 1 (0)| 00:00:01 | | | | |
|* 13 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | | 1 (0)| 00:00:01 | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | BS_CORP_PRES | 1 | 8 | 1 (0)| 00:00:01 | | | | |
|* 15 | INDEX RANGE SCAN | INDEX_BS_CORP_PRES_INDI | 1 | | 1 (0)| 00:00:01 | | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | 1 (0)| 00:00:01 | | | | |
|* 17 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | 1 (0)| 00:00:01 | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | MT_APPLY | 1 | 9 | 1 (0)| 00:00:01 | | | | |
|* 19 | INDEX UNIQUE SCAN | PK_MT_APPLY | 1 | | 1 (0)| 00:00:01 | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | 1 (0)| 00:00:01 | | | | |
|* 21 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | 1 (0)| 00:00:01 | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | BS_TREAT_TYPE | 1 | 23 | 1 (0)| 00:00:01 | | | | |
|* 23 | INDEX UNIQUE SCAN | PK_BS_TREAT_TYPE | 1 | | 1 (0)| 00:00:01 | | | | |
| 24 | SORT AGGREGATE | | 1 | 34 | | | | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | MT_FEE_STAT_FIN | 1 | 34 | 1 (0)| 00:00:01 | | | | |
|* 26 | INDEX RANGE SCAN | PK_MT_FEE_STAT_FIN | 1 | | 1 (0)| 00:00:01 | | | | |
| 27 | SORT AGGREGATE | | 1 | 31 | | | | | | |
|* 28 | TABLE ACCESS BY INDEX ROWID | MT_FEE_STAT_FIN | 1 | 31 | 1 (0)| 00:00:01 | | | | |
|* 29 | INDEX RANGE SCAN | PK_MT_FEE_STAT_FIN | 1 | | 1 (0)| 00:00:01 | | | | |
| 30 | VIEW | | 504 | 771K| 1923 (2)| 00:00:24 | | | | |
| 31 | SORT ORDER BY | | 504 | 431K| 1923 (2)| 00:00:24 | 5935K| 992K| 1/0/0| |
| 32 | COUNT | | | | | | | | | |
| 33 | NESTED LOOPS | | 504 | 431K| 1922 (2)| 00:00:24 | | | | |
|* 34 | HASH JOIN | | 504 | 428K| 1921 (2)| 00:00:24 | 1095K| 1095K| 2/0/0| |
| 35 | TABLE ACCESS FULL | BS_BIZTYPE | 97 | 1164 | 3 (0)| 00:00:01 | | | | |
|* 36 | HASH JOIN RIGHT OUTER | | 504 | 422K| 1918 (2)| 00:00:24 | 1078K| 1078K| 2/0/0| |
| 37 | TABLE ACCESS FULL | BS_CENTER | 12 | 168 | 3 (0)| 00:00:01 | | | | |
|* 38 | HASH JOIN RIGHT OUTER | | 504 | 415K| 1914 (1)| 00:00:23 | 1593K| 1593K| 2/0/0| |
| 39 | VIEW | | 1 | 12 | 117 (2)| 00:00:02 | | | | |
|* 40 | HASH JOIN | | 1 | 18 | 117 (2)| 00:00:02 | 1133K| 1133K| 2/0/0| |
| 41 | TABLE ACCESS FULL | BS_COUNTRY_STREET | 1 | 7 | 2 (0)| 00:00:01 | | | | |
|* 42 | TABLE ACCESS FULL | BS_CORP | 9523 | 102K| 114 (1)| 00:00:02 | | | | |
|* 43 | HASH JOIN | | 504 | 409K| 1797 (1)| 00:00:22 | 915K| 915K| 2/0/0| |
| 44 | INDEX FULL SCAN | IDX_BS_HOSPITAL_NAME | 1325 | 43725 | 2 (0)| 00:00:01 | | | | |
|* 45 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 225 | 1 (0)| 00:00:01 | | | | |
| 46 | NESTED LOOPS | | 630 | 491K| 1794 (1)| 00:00:22 | | | | |
| 47 | VIEW | | 638 | 357K| 1539 (2)| 00:00:19 | | | | |
| 48 | HASH GROUP BY | | 638 | 37642 | 1539 (2)| 00:00:19 | 10M| 1830K| | 17408 |
| 49 | VIEW | | 638 | 37642 | 1539 (2)| 00:00:19 | | | | |
| 50 | HASH UNIQUE | | 638 | 122K| 1539 (2)| 00:00:19 | 32M| 3970K| | 30720 |
| 51 | NESTED LOOPS | | 638 | 122K| 1538 (2)| 00:00:19 | | | | |
| 52 | NESTED LOOPS | | 637 | 111K| 1537 (2)| 00:00:19 | | | | |
| 53 | NESTED LOOPS | | 637 | 97K| 1536 (2)| 00:00:19 | | | | |
| 54 | NESTED LOOPS | | 642 | 60990 | 1407 (2)| 00:00:17 | | | | |
| 55 | NESTED LOOPS | | 651 | 26691 | 1147 (2)| 00:00:14 | | | | |
|* 56 | TABLE ACCESS FULL | PM_BILL | 3 | 27 | 1119 (2)| 00:00:14 | | | | |
|* 57 | TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ | 205 | 6560 | 9 (0)| 00:00:01 | | | | |
|* 58 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_MONTH | 213 | | 1 (0)| 00:00:01 | | | | |
|* 59 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 54 | 1 (0)| 00:00:01 | | | | |
|* 60 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | | | |
|* 61 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 62 | 1 (0)| 00:00:01 | | | | |
|* 62 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | 1 (0)| 00:00:01 | | | | |
|* 63 | INDEX UNIQUE SCAN | PK_BS_BIZTYPE | 1 | 22 | 1 (0)| 00:00:01 | | | | |
|* 64 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 18 | 1 (0)| 00:00:01 | | | | |
|* 65 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | | | |
|* 66 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2
2 - SEL$2 / T@SEL$2
3 - SEL$2 / T@SEL$2
4 - SEL$3
5 - SEL$3 / T@SEL$3
6 - SEL$3 / T@SEL$3
7 - SEL$7 / BS@SEL$7
8 - SEL$7 / BS@SEL$7
9 - SEL$8 / PP@SEL$8
10 - SEL$8 / PP@SEL$8
11 - SEL$15
12 - SEL$15 / A@SEL$15
13 - SEL$15 / A@SEL$15
14 - SEL$4 / T@SEL$4
15 - SEL$4 / T@SEL$4
16 - SEL$9 / T@SEL$9
17 - SEL$9 / T@SEL$9
18 - SEL$11 / T@SEL$11
19 - SEL$11 / T@SEL$11
20 - SEL$10 / Q@SEL$10
21 - SEL$10 / Q@SEL$10
22 - SEL$5 / BS_TREAT_TYPE@SEL$5
23 - SEL$5 / BS_TREAT_TYPE@SEL$5
24 - SEL$16
25 - SEL$16 / SS@SEL$16
26 - SEL$16 / SS@SEL$16
27 - SEL$17
28 - SEL$17 / SS@SEL$17
29 - SEL$17 / SS@SEL$17
30 - SEL$6 / W@SEL$1
31 - SEL$6
35 - SEL$6 / G@SEL$6
37 - SEL$6 / H@SEL$6
39 - SEL$8F7BCF6F / STREET@SEL$6
40 - SEL$8F7BCF6F
41 - SEL$8F7BCF6F / ST@SEL$12
42 - SEL$8F7BCF6F / CORP@SEL$12
44 - SEL$6 / A@SEL$6
45 - SEL$6 / B@SEL$6
47 - SEL$6C11BF2C / T@SEL$6
48 - SEL$6C11BF2C
49 - SEL$93984FCC / $vm_view@SEL$6C11BF2C
50 - SEL$93984FCC
56 - SEL$93984FCC / PM_BILL@SEL$19
57 - SEL$93984FCC / PM_ACCOUNT_BIZ@SEL$18
58 - SEL$93984FCC / PM_ACCOUNT_BIZ@SEL$18
59 - SEL$93984FCC / B@SEL$14
60 - SEL$93984FCC / B@SEL$14
61 - SEL$93984FCC / C@SEL$14
62 - SEL$93984FCC / C@SEL$14
63 - SEL$93984FCC / G@SEL$14
64 - SEL$93984FCC / J@SEL$14
65 - SEL$6 / B@SEL$6
66 - SEL$6 / J@SEL$6
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."VALID_FLAG"='1')
3 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='430701')
filter("T"."CENTER_ID"='430701')
5 - filter("T"."VALID_FLAG"='1')
6 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='0')
filter("T"."CENTER_ID"='0')
8 - access("BS"."SEX"=TO_NUMBER(:B1))
10 - access("PP"."PERS_TYPE"=TO_NUMBER(:B1) AND "PP"."CENTER_ID"=:B2)
13 - access("A"."INDI_ID"=:B1)
15 - access("T"."INDI_ID"=:B1)
17 - access("T"."CENTER_ID"=NVL(:B1,:B2) AND "T"."ICD"=:B3)
19 - access("T"."SERIAL_APPLY"=:B1)
21 - access("Q"."CENTER_ID"=NVL(:B1,:B2) AND "Q"."ICD"=:B3)
23 - access("TREATMENT_TYPE"=:B1 AND "CENTER_ID"=:B2)
25 - filter("SS"."VALID_FLAG"='1')
26 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
filter(("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003'))
28 - filter("SS"."VALID_FLAG"='1')
29 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
filter(("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003'))
34 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
36 - access("B"."CENTER_ID"="H"."CENTER_ID")
38 - access("B"."CORP_ID"="STREET"."CORP_ID")
40 - access("CORP"."AREA_CODE"="ST"."QYBM")
42 - filter("CORP"."AREA_CODE" IS NOT NULL)
43 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
45 - filter("B"."VALID_FLAG"='1')
56 - filter("OUTPAY_BILL_NO"=23)
57 - filter(("BIZ_TYPE"<>'12' OR ("BIZ_FLAG"=18 AND "BIZ_TYPE"='12')))
58 - access("MONTH_DECL_SN"="RELA_DECL_SN")
59 - filter("B"."VALID_FLAG"='1')
60 - access("B"."HOSPITAL_ID"="HOSPITAL_ID" AND "B"."SERIAL_NO"="SERIAL_NO")
61 - filter("C"."VALID_FLAG"='1')
62 - access("B"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "B"."SERIAL_NO"="C"."SERIAL_NO")
63 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
64 - access("B"."INDI_ID"="J"."INDI_ID")
65 - access("B"."HOSPITAL_ID"="T"."HOSPITAL_ID" AND "B"."SERIAL_NO"="T"."SERIAL_NO")
66 - access("B"."INDI_ID"="J"."INDI_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."POLICY_VALUE"[VARCHAR2,200]
2 - "T"."POLICY_VALUE"[VARCHAR2,200]
3 - "T".ROWID[ROWID,10]
4 - "T"."POLICY_VALUE"[VARCHAR2,200]
5 - "T"."POLICY_VALUE"[VARCHAR2,200]
6 - "T".ROWID[ROWID,10]
7 - "BS".ROWID[ROWID,10], "BS"."SEX_NAME"[VARCHAR2,5]
8 - "BS".ROWID[ROWID,10]
9 - "PP".ROWID[ROWID,10], "PP"."PERS_NAME"[VARCHAR2,20]
10 - "PP".ROWID[ROWID,10]
11 - (#keys=0) MAX("INSR_CODE")[30]
12 - "INSR_CODE"[VARCHAR2,30]
13 - "A".ROWID[ROWID,10]
14 - "T".ROWID[ROWID,10], "T"."SPECIAL_CODE"[NUMBER,22]
15 - "T".ROWID[ROWID,10]
16 - "T".ROWID[ROWID,10], "T"."DISEASE"[VARCHAR2,100]
17 - "T".ROWID[ROWID,10]
18 - "T".ROWID[ROWID,10], "T"."APPLY_CONTENT"[VARCHAR2,3]
19 - "T".ROWID[ROWID,10]
20 - "Q".ROWID[ROWID,10], "Q"."DISEASE"[VARCHAR2,100]
21 - "Q".ROWID[ROWID,10]
22 - "BS_TREAT_TYPE".ROWID[ROWID,10], "TREATMENT_NAME"[VARCHAR2,50]
23 - "BS_TREAT_TYPE".ROWID[ROWID,10]
24 - (#keys=0) SUM("SS"."PART_SELF_MONEY")[22], SUM("SS"."ALL_SELF_MONEY")[22]
25 - "SS"."PART_SELF_MONEY"[NUMBER,22], "SS"."ALL_SELF_MONEY"[NUMBER,22]
26 - "SS".ROWID[ROWID,10]
27 - (#keys=0) SUM("SS"."MONEY")[22]
28 - "SS"."MONEY"[NUMBER,22]
29 - "SS".ROWID[ROWID,10]
30 - "ROWNO"[NUMBER,22], "HOS_SERIAL"[VARCHAR2,30], "REG_FLAG"[CHARACTER,1], "DISEASE_TYPE"[CHARACTER,1], "HOSPITAL_ID"[VARCHAR2,20],
"HOSPITAL_NAME"[VARCHAR2,70], "SERIAL_NO"[VARCHAR2,16], "BIZ_TYPE"[VARCHAR2,2], "CASE_ID"[NUMBER,22], "BIZ_STAT"[VARCHAR2,2], "NAME"[VARCHAR2,20],
"SEX"[VARCHAR2,5], "PERS_TYPE"[VARCHAR2,20], "BEGIN_DATE"[VARCHAR2,10], "END_DATE"[VARCHAR2,10], "FIN_DATE"[VARCHAR2,19], "FINISH_DATE"[VARCHAR2,19],
"INDI_ID"[NUMBER,22], "INSR_CODE"[VARCHAR2,30], "FIN_STAFF"[VARCHAR2,10], "FIN_MAN"[VARCHAR2,30], "CORP_ID"[NUMBER,22], "IDCARD"[VARCHAR2,25],
"REG_DATE"[VARCHAR2,19], "IN_DAYS"[NUMBER,22], "DISTRICT_CODE"[VARCHAR2,6], "CENTER_ID"[VARCHAR2,10], "OFFICE_GRADE"[VARCHAR2,3],
"CORP_NAME"[VARCHAR2,70], "DISEASE"[VARCHAR2,100], "IN_AREA_NAME"[VARCHAR2,20], "IN_DEPT_NAME"[VARCHAR2,20], "IN_BED"[VARCHAR2,10],
"BED_TYPE"[CHARACTER,1], "PATIENT_ID"[VARCHAR2,20], "REMARK"[VARCHAR2,1000], "POS_CODE"[VARCHAR2,10], "REIMBURSE_FLAG"[CHARACTER,1],
"FIN_DISEASE"[VARCHAR2,100], "FOREGIFT"[NUMBER,1], "IC_NO"[VARCHAR2,25], "TREATMENT_TYPE"[VARCHAR2,3], "APPLY_CONTENT"[VARCHAR2,3],
"REG_MAN"[VARCHAR2,20], "W"."AREA_CODE"[VARCHAR2,10], "FEES"[NUMBER,22], "PAY_MONEY_JD"[NUMBER,22], "FUND_MONEY"[NUMBER,22], "FUND_301"[NUMBER,22],
"FUND_003"[NUMBER,22], "QFX"[NUMBER,22], "HOSP_ZF"[NUMBER,22], "CENTER_ZF"[NUMBER,22], "YW_FUND_MONEY"[NUMBER,22], "BC_FUND_MONEY"[NUMBER,22],
"PAY_MONEY_ALLSELF"[NUMBER,22], "PAY_MONEY_SELF"[NUMBER,22], "PAY_MONEY_E00SELF"[NUMBER,22], "PAY_MONEY_Z00SELF"[NUMBER,22],
"PAY_MONEY_S00SELF"[NUMBER,22], "PAY_MONEY_S01SELF"[NUMBER,22], "PAY_MONEY_C000SELF"[NUMBER,22], "PAY_MONEY_C001SELF"[NUMBER,22],
"PAY_MONEY_C004SELF"[NUMBER,22], "PAY_MONEY_C006SELF"[NUMBER,22], "PAY_MONEY_C007SELF"[NUMBER,22], "PAY_MONEY_C007003SELF"[NUMBER,22],
"DB_MONEY"[NUMBER,22], "YWSH_DB_MONEY"[NUMBER,1], "TC_MONEY"[NUMBER,22], "TC_MONEY_XE"[NUMBER,22], "HOSP_PAY"[NUMBER,22], "HOSP_PRISE"[NUMBER,22],
"JMTC_MONEY"[NUMBER,22], "YW_MONEY"[NUMBER,22], "JMYW_MONEY"[NUMBER,22], "ACCT_MONEY"[NUMBER,22], "LX_MONEY"[NUMBER,22], "SY_MONEY"[NUMBER,22],
"OFFI_MONEY"[NUMBER,22], "ZHAOGU_PAY"[NUMBER,22], "PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
"REG_INFO"[VARCHAR2,10], "DB_MONEY_JUMIN"[NUMBER,22]
31 - (#keys=1) ROWNUM[22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."REG_FLAG"[CHARACTER,1],
"B"."DISEASE_TYPE"[CHARACTER,1], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."BIZ_TYPE"[VARCHAR2,2], "B"."CASE_ID"[NUMBER,22], "G"."BIZ_STAT"[VARCHAR2,2], "B"."NAME"[VARCHAR2,20], [5], [20],
TO_CHAR(INTERNAL_FUNCTION("B"."BEGIN_DATE"),'yyyy-mm-dd')[10], TO_CHAR(INTERNAL_FUNCTION("B"."END_DATE"),'yyyy-mm-dd')[10],
TO_CHAR(INTERNAL_FUNCTION("B"."FIN_DATE"),'yyyy-mm-dd hh24:mi:ss')[19], TO_CHAR(INTERNAL_FUNCTION("B"."FIN_DATE"),'yyyy-mm-dd hh24:mi:ss')[19],
"B"."INDI_ID"[NUMBER,22], "T"."INSR_CODE"[VARCHAR2,30], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CORP_ID"[NUMBER,22],
"B"."IDCARD"[VARCHAR2,25], TO_CHAR(INTERNAL_FUNCTION("B"."REG_DATE"),'yyyy-mm-dd hh24:mi:ss')[19], NVL("B"."IN_DAYS",0)[22],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."CENTER_ID"[VARCHAR2,10], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."CORP_NAME"[VARCHAR2,70],
"B"."IN_DISEASE"[VARCHAR2,20], [100], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
','')[1000], "B"."POS_CODE"[VARCHAR2,10],PATIENT_ID"[VARCHAR2,20], TRANSLATE("B"."REMARK",'
"B"."REIMBURSE_FLAG"[CHARACTER,1], [100], 0[1], "B"."IC_NO"[VARCHAR2,25], "B"."TREATMENT_TYPE"[VARCHAR2,3], [3], "B"."REG_MAN"[VARCHAR2,20],
"STREET"."QYBM"[VARCHAR2,10], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
"T"."FUND_003"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
"T"."BC_FUND_MONEY"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
"T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
"T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
"T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"-"T"."YWSH_DB_MONEY"[22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22],
"T"."TC_MONEY_XE"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22],
"T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22],
"T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
"B"."REG_INFO"[VARCHAR2,10]
32 - "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
"B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
"A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
"T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
"T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
"T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
"T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
"T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
"T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
"T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
"T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
"MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1],
"B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7],
"B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10],
"B"."FIN_MAN"[VARCHAR2,30], ROWNUM[4]
33 - "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
"B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
"A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
"T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
"T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
"T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
"T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
"T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
"T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
"T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
"T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
"MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1],
"B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7],
"B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10],
"B"."FIN_MAN"[VARCHAR2,30]
34 - (#keys=2) "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
"B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
"A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
"T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
"T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
"T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
"T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
"T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
"T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
"T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
"T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
"MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1],
"B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7],
"B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10],
"B"."FIN_MAN"[VARCHAR2,30]
35 - "G"."CENTER_ID"[VARCHAR2,10], "G"."BIZ_TYPE"[CHARACTER,2], "G"."BIZ_STAT"[VARCHAR2,2]
36 - (#keys=1) "H"."CENTER_ID"[VARCHAR2,10], "B"."CENTER_ID"[VARCHAR2,10], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22],
"STREET"."QYBM"[VARCHAR2,10], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
"T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
"T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
"T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
"T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
"T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
"T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
"T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
"T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
"MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20],
"B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30],
"B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20],
"B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20],
"B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22],
"B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500],
"B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3]
37 - "H"."CENTER_ID"[VARCHAR2,10], "H"."CATALOG_CENTER"[VARCHAR2,10]
38 - (#keys=1) "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70],
"T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
"T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22],
"T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
"T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
"T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
"T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22],
"T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22],
"T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22],
"T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22],
"B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6],
"B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25],
"B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22],
"B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10],
"B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20],
"B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1],
"B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30],
"B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3]
39 - "STREET"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10]
40 - (#keys=1) "ST"."QYBM"[VARCHAR2,10], "CORP"."CORP_ID"[NUMBER,22]
41 - "ST"."QYBM"[VARCHAR2,10]
42 - "CORP"."CORP_ID"[NUMBER,22], "CORP"."AREA_CODE"[VARCHAR2,20]
43 - (#keys=1) "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
"T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
"T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
"T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
"T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
"T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
"T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
"T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
"T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
"MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20],
"B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."CORP_ID"[NUMBER,22],
"B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20],
"B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20],
"B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22],
"B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500],
"B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3],
"B"."HOS_SERIAL"[VARCHAR2,30]
44 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70]
45 - "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2],
"B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
"B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."CORP_ID"[NUMBER,22], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
"B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
"B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1],
"B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7],
"B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10],
"B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "B"."HOS_SERIAL"[VARCHAR2,30],
"B"."DISEASE_TYPE"[CHARACTER,1]
46 - "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
"T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22],
"T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
"T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
"T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
"T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22],
"T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22],
"T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22],
"T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22],
"SYS_ALIAS_10".ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]
47 - "T"."HOSPITAL_ID"[VARCHAR2,20], "T"."SERIAL_NO"[VARCHAR2,16], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
"T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
"T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
"T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
"T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
"T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
"T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
"T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
"T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
"MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22]
48 - "$vm_col_9"[VARCHAR2,20], "$vm_col_10"[VARCHAR2,16], "$vm_col_8"[NUMBER,22], SUM(DECODE("$vm_col_6",'803',"$vm_col_5",0))[22], SUM(CASE WHEN
(("$vm_col_4"='C000' OR "$vm_col_4"='C001' OR "$vm_col_4"='C002' OR "$vm_col_4"='C003' OR "$vm_col_4"='C004') AND ("$vm_col_6"='999' OR
"$vm_col_6"='003')) THEN "$vm_col_5" ELSE 0 END )[22], SUM(DECODE("$vm_col_6",'999',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_6",'401',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'301',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'511',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_6",'202',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'003',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'802',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_6",'901',"$vm_col_5",'802',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'801',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_6",'001',"$vm_col_5",'801',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'996',NVL("$vm_col_5",0),0))[22],
SUM(DECODE("$vm_col_6",'201',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C007003',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_4"||"$vm_col_6",'C007999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C006003',"$vm_col_5",'C006999',"$vm_col_5",'C0070
03',"$vm_col_5",'C007999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C004003',"$vm_col_5",'C004999',"$vm_col_5",'C005003',"$vm_col_5",'C00
5999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C001003',"$vm_col_5",'C001999',"$vm_col_5",'C002003',"$vm_col_5",'C002999',"$vm_col_5",'C
003003',"$vm_col_5",'C003999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C000003',"$vm_col_5",'C000999',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_4"||"$vm_col_6",'S02003',"$vm_col_5",'S02999',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_4"||"$vm_col_6",'S01003',"$vm_col_5",'S01999',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_4"||"$vm_col_6",'S00003',"$vm_col_5",'S00999',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_4"||"$vm_col_6",'Z00003',"$vm_col_5",'Z00999',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_4"||"$vm_col_6",'E00003',"$vm_col_5",'E00999',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_3"||"$vm_col_6",'102003',"$vm_col_5",'102999',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_3"||"$vm_col_6",'101003',"$vm_col_5",'101999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'998',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_6",'996',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4",'S01',"$vm_col_5",'S02',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_6",'306',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'001',"$vm_col_5",'511',"$vm_col_5",'202',"$vm_col_5",'801',"$vm_col_5",0))[22],
SUM(DECODE("$vm_col_4",'C000',"$vm_col_5",'C001',"$vm_col_5",'C002',"$vm_col_5",'C003',"$vm_col_5",'C004',"$vm_col_5",'C005',"$vm_col_5",'C006',"$vm_col
_5",'C007',"$vm_col_5",0))[22], SUM("$vm_col_5")[22]
49 - "$vm_col_9"[VARCHAR2,20], "$vm_col_10"[VARCHAR2,16], "$vm_col_8"[NUMBER,22], "$vm_col_3"[VARCHAR2,3], "$vm_col_4"[VARCHAR2,20],
"$vm_col_5"[NUMBER,22], "$vm_col_6"[VARCHAR2,3]
50 - "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."INDI_ID"[NUMBER,22], ROWID[ROWID,10], ROWID[ROWID,10], ROWID[ROWID,10],
ROWID[ROWID,10], "SERIAL_NO"[VARCHAR2,16], "HOSPITAL_ID"[VARCHAR2,20], "C"."LABEL_FLAG"[VARCHAR2,3], "C"."POLICY_ITEM_CODE"[VARCHAR2,20],
"C"."REAL_PAY"[NUMBER,22], "C"."FUND_ID"[VARCHAR2,3], "FINISH_FLAG"[CHARACTER,1]
51 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3],
"C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3], ROWID[ROWID,10], ROWID[ROWID,10]
52 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3],
"C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3], ROWID[ROWID,10]
53 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10], ROWID[ROWID,10],
"C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3], "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3]
54 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
"B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10]
55 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16]
56 - "RELA_DECL_SN"[NUMBER,22]
57 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16]
58 - "PM_ACCOUNT_BIZ".ROWID[ROWID,10]
59 - ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22],
"FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10]
60 - ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]
61 - ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3], "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3]
62 - ROWID[ROWID,10]
63 - ROWID[ROWID,10]
64 - ROWID[ROWID,10]
65 - "SYS_ALIAS_10".ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
458 rows selected.
修改后执行时间为几秒钟，在业务需求可接受范围内，到此达到了优化目标。引起问题的原因很简单，就是开发人员根本不知道谓词条件where (a||b) in(select a||b from c)与where (a,b) in(select a,b from c)之间的差别,差别在于，如果存在复合索引(a,b..), 那么(a||b)这种写法用不了索引，当表的数据量很大时它与使用索引的写法(a,b)性能差异是巨大的。