某系统维护人员在执行一查询需要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)性能差异是巨大的。