mybatis 查询大量数据 效率低

下面是我的mapper文件，查询超级慢，请问有什么办法优化吗

 <select id="selectMedAlls" resultMap="BaseResultMap" >
with temp as
 (select FUN_GET_DEPT_NAME(a.nurse_cell_code) as nurse_cell_name,
         month_code,
         sum(a.out_num) as out_num, /*出院人数*/
         sum(a.bednum) as sjkfzcrs /*实际开放总床日数*/,
         round((sum(a.bednum) / 30), 1) as pjkfcws /*平均开发床位数*/,
         sum(a.end_num) as sjzyzcws /*实际占用总床日数*/ /*'期末人数'*/
    from nurse_day_report a
   where a.month_code between '201501' and '201511'
   group by a.nurse_cell_code, month_code
   order by a.nurse_cell_code),
   data as (
select 'thisM' as time ,
nvl(max((select count(1) as aa
             from fin_opr_register t1
            WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
              and t1.valid_flag = '1'
              and t1.reglevl_name <![CDATA[<>]]> ' '
              and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
       nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
       nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
       nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
       nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
  from temp
  where month_code='201511'

union all
select 'lastM' ,
nvl(max((select count(1) as aa
             from fin_opr_register t1
            WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
              and t1.valid_flag = '1'
              and t1.reglevl_name <![CDATA[<>]]> ' '
              and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
       nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
       nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
       nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
       nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
  from temp
  where month_code='201511'
  union all
  select 'thisY' ,
nvl(max((select count(1) as aa
             from fin_opr_register t1
            WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
              and t1.valid_flag = '1'
              and t1.reglevl_name <![CDATA[<>]]> ' '
              and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
       nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
       nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
       nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
       nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
  from temp
  where month_code between '201601' and '201511'
  union all
  select 'lastY' ,
nvl(max((select count(1) as aa
             from fin_opr_register t1
            WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
              and t1.valid_flag = '1'
              and t1.reglevl_name <![CDATA[<>]]> ' '
              and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
       nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
       nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
       nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
       nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
  from temp
  where  month_code between '201501' and '201511'
)
select 
sum(case when TIME = 'thisM' then regist else 0 end) as "thisM",
sum(case when TIME = 'lastM' then regist else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then regist else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then regist else 0 end)-sum(case when TIME = 'lastM' then regist else 0 end))*100/sum(case when TIME = 'lastM' then regist else 0 end),3))as lvM,
sum(case when TIME = 'thisY' then regist else 0 end) as "thisY",
sum(case when TIME = 'lastY' then regist else 0 end) as "lastY" ,
decode(sum(case when TIME = 'lastY' then regist else 0 end) ,0,0,round((sum(case when TIME = 'thisY' then regist else 0 end) -sum(case when TIME = 'lastY' then regist else 0 end) )*100/sum(case when TIME = 'lastY' then regist else 0 end)  ,1))as lvY
from data
union all
select 
sum(case when TIME = 'thisM' then outer else 0 end) as "thisM",
sum(case when TIME = 'lastM' then outer else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then outer else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then outer else 0 end)-sum(case when TIME = 'lastM' then outer else 0 end))*100/sum(case when TIME = 'lastM' then outer else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then outer else 0 end) as "thisY",
sum(case when TIME = 'lastY' then outer else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then outer else 0 end),0,0,round((sum(case when TIME = 'thisY' then outer else 0 end)-sum(case when TIME = 'lastY' then outer else 0 end))*100/sum(case when TIME = 'lastY' then outer else 0 end),1) ) as LvY
from data

union all
select 
sum(case when TIME = 'thisM' then sjzyzcrs else 0 end) as "thisM",
sum(case when TIME = 'lastM' then sjzyzcrs else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then sjzyzcrs else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then sjzyzcrs else 0 end)-sum(case when TIME = 'lastM' then sjzyzcrs else 0 end))*100/sum(case when TIME = 'lastM' then sjzyzcrs else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then sjzyzcrs else 0 end) as "thisY",
sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) ,0,0,round((sum(case when TIME = 'thisY' then sjzyzcrs else 0 end)-sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) )*100/sum(case when TIME = 'lastY' then sjzyzcrs else 0 end)  ,1)) as LvY
 from data
union all
select 
sum(case when TIME = 'thisM' then zzcs else 0 end) as "thisM",
sum(case when TIME = 'lastM' then zzcs else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then zzcs else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then zzcs else 0 end)-sum(case when TIME = 'lastM' then zzcs else 0 end))*100/sum(case when TIME = 'lastM' then zzcs else 0 end),1))  as LvM,
sum(case when TIME = 'thisY' then zzcs else 0 end) as "thisY",
sum(case when TIME = 'lastY' then zzcs else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then zzcs else 0 end),0,0,round((sum(case when TIME = 'thisY' then zzcs else 0 end)-sum(case when TIME = 'lastY' then zzcs else 0 end))*100/sum(case when TIME = 'lastY' then zzcs else 0 end) ,1)) as LvY
from data
union all
select 
sum(case when TIME = 'thisM' then cwsyl else 0 end) as "thisM",
sum(case when TIME = 'lastM' then cwsyl else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then cwsyl else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then cwsyl else 0 end)-sum(case when TIME = 'lastM' then cwsyl else 0 end))*100/sum(case when TIME = 'lastM' then cwsyl else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then cwsyl else 0 end) as "thisY",
sum(case when TIME = 'lastY' then cwsyl else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then cwsyl else 0 end),0,0,round((sum(case when TIME = 'thisY' then cwsyl else 0 end)-sum(case when TIME = 'lastY' then cwsyl else 0 end))*100/sum(case when TIME = 'lastY' then cwsyl else 0 end) ,1)) as LvY
from data
  </select>

查询出的结果是

http://blog.csdn.net/happylee6688/article/details/45967763

表上有索引的话可以用基于索引的连接方法