sql语句优化

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;