转载

百倍性能提升:谓词条件(a||b) vs (a,b)

某系统维护人员在执行一查询需要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
百倍性能提升:谓词条件(a||b) vs (a,b)


百倍性能提升:谓词条件(a||b) vs (a,b)

从sql报告来看
百倍性能提升:谓词条件(a||b) vs (a,b)

百倍性能提升:谓词条件(a||b) vs (a,b)

执行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需要几分钟才能执行完成
百倍性能提升:谓词条件(a||b) vs (a,b)
查看其执行计划

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. 

百倍性能提升:谓词条件(a||b) vs (a,b)

百倍性能提升:谓词条件(a||b) vs (a,b)

百倍性能提升:谓词条件(a||b) vs (a,b)

使用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秒以内:
百倍性能提升:谓词条件(a||b) vs (a,b)

百倍性能提升:谓词条件(a||b) vs (a,b)

查看其执行计划:

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)性能差异是巨大的。

正文到此结束
Loading...