sql语句优化

?

1 2 3

<br>...<br>AAA, newbi.dim_date dd where AAA.FEE_DATE = dd.ddate

以上方式在存储过程中执行速度很慢，改为以下sql后速度提升明显：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

select NVL(SUM(DRUG_COST), 0) DRUG_COST, --dd.id FEE_DATE_ID, (select dd.id from newbi.dim_date dd where AAA.FEE_DATE = dd.ddate) FEE_DATE_ID, nvl((select T.id from newbi.dim_drug_type T where T.drug_code = AAA.drug_code), 0) DRUG_ID, nvl((select t.id from newbi.dim_dept T where T.dept_code = AAA.dept_code), 0) DEPT_ID, nvl((select T.id from newbi.dim_employee T where T.empl_code = AAA.doct_code), 0) DOCT_ID, nvl((select T.id from newbi.dim_visit_type T where T.VISIT_CODE = AAA.VISIT_CODE), 0) VISIT_TYPE_ID, nvl((select T.id from newbi.dim_insure_type T where T.INSURE_TYPE_CODE = AAA.pact_code), 0) INSURE_TYPE_ID --2018-07-25 lr 新增保险类型 from ( select (A.PAY_COST + A.OWN_COST + A.PUB_COST) DRUG_COST, TRUNC(FEE_DATE) FEE_DATE, a.item_code drug_code, REG_DPCD dept_code, a.DOCT_CODE doct_code, case when b.is_emergency = '1' then '02' --急诊 else '01' --门诊 end VISIT_CODE, b.pact_code from newods.cli_pay a, newods.cli_registe b where a.clinic_code = b.clinic_code and a.drug_flag = '1' and a.fee_date >= V_Begintime AND a.fee_date < V_Endtime union all select a.tot_cost DRUG_COST, TRUNC(FEE_DATE) FEE_DATE, a.item_code drug_code, RECIPE_DPCD dept_code, a.RECIPE_DOCCODE doct_code, '03' VISIT_CODE, a.pact_code from newods.inh_pay a where a.drug_flag = '1' and a.fee_date >= V_Begintime AND a.fee_date < V_Endtime) AAA--, --newbi.dim_date dd --where AAA.FEE_DATE = dd.ddate group by --dd.id, AAA.FEE_DATE, AAA.drug_code, AAA.dept_code, AAA.doct_code, AAA.pact_code, AAA.visit_code;