oracle 物化视图查询慢,为何用户物化视图后查询比之前的慢了

select

s.recipe_cate_text,

sum(s.all_dosing_recipe) all_dosing_recipe_count,

sum(s.is_cancel_dosing_recipe) cancel_dosing_recipt_count,

sum(s.all_dosing_recipe) - sum(s.is_cancel_dosing_recipe) dosing_recipe_count,

sum(case when s.state=2 then s.amount else 0 end) dosing_recipe_money,

s.clinic_dept_code,

max(s.clinic_dept_text) clinic_dept_text

from (

select (

select

max(mvdrug.drugcategorytext)

from

OUTPATIENT_RECIPE_DETAIL rd

left join mv_drugcategory mvdrug on rd.durg_id = mvdrug.drug_info_id

/*

left join oms_drug_info drug on rd.durg_id = drug.drug_info_id

left join oms_drug_category drug_cat on drug.drug_category_id = drug_cat.drug_category_id

*/

where

rd.recipe_id = r.recipe_id

) recipe_cate_text,

case when r.state in (2,3) then 1 else 0 end all_dosing_recipe, -- 所有已投药处方

case when r.state = 3 then 1 else 0 end is_cancel_dosing_recipe, -- 已退药处方

r.state,

r.amount,

d.code clinic_dept_code,

d.text clinic_dept_text

from

outpatient_recipe r

left join outpatient_registration g on r.registration_id = g.registration_id

left join oms_general_dept d on g.dept = d.general_dept_id

where

r.is_del = 0

and r.state in (2,3) -- 已投药，已退药

and r.dosing_time >= to_date('2013-01-24 00:00:00','yyyy-mm-dd hh24:mi:ss')

and r.dosing_time <= to_date('2013-06-24 23:59:59','yyyy-mm-dd hh24:mi:ss')

and r.dosing_dept2 =  '51c6013d-ef8c-4b98-bd60-fac1c2b40db8'  ) s

group by s.clinic_dept_code, s.recipe_cate_text

order by s.clinic_dept_code, s.recipe_cate_text;

这是更改的的查询