как оптимизировать запрос?

select e.eriala_nimetus, count(e.id) as esmased, count(k.id) as korduv, (count(e.id) + count(k.id)) as kokku_avatud,count(el.id) as esmane_lisa_kohal, count(ek.id) as esmane_kohal, count(kl.id) as korduv_lisa_kohal,count(kk.id) as korduv_kohal, (count(el.id) + count(ek.id) + count(kl.id) + count(kk.id)) as kokku_kohal,( 100 * (count(e.id) + count(k.id))/case when (count(el.id) + count(ek.id) + count(kl.id) + count(kk.id)) = 0 then 1 else (count(el.id) + count(ek.id) + count(kl.id) + count(kk.id)) end) as kohaloleku_prots,count(bk.id) as mitte_kasutatud_broon, 0 as hind, 0 as progn_kaive, 0 as korreg_kaive from visiidid v left join erialad e on v.eriala_kood = e.kood left join(select * from visiidid where tyyp_kood = 'V1' and lisanumber is null) e on v.id = e.id left join (select * from visiidid where tyyp_kood = 'V2' and lisanumber is null) k on v.id = k.id left join (select * from visiidid where tyyp_kood = 'V1' and lisanumber = 'J' and kohal = 'J') el on v.id = el.id left join (select * from visiidid where tyyp_kood = 'V2' and lisanumber = 'J') kl on v.id = kl.id left join (select * from visiidid where tyyp_kood = 'V1' and kohal = 'J' and lisanumber is null) ek on v.id = ek.id left join (select * from visiidid where tyyp_kood = 'V2' and kohal = 'J' and lisanumber is null) kk on v.id = kk.id left join (select * from visiidid where tyyp_kood in ('V1', 'V2') and lisanumber is not null and kohal is null) bk on v.id = bk.id WHERE v.algus BETWEEN TO_DATE('2006-05-01', 'YYYY-MM-DD') AND TO_DATE('2006-05-24', 'YYYY-MM-DD') and v.staatus not in ('P', 'A') and asukoht_id IN (1,81) GROUP BY e.eriala_nimetus

select e.eriala_nimetus, count(e.id) as esmased, count(k.id) as korduv, (count(e.id) + count(k.id)) as kokku_avatud,
count(el.id) as esmane_lisa_kohal, count(ek.id) as esmane_kohal, count(kl.id) as korduv_lisa_kohal,
count(kk.id) as korduv_kohal, (count(el.id) + count(ek.id) + count(kl.id) + count(kk.id)) as kokku_kohal,
( 100 * (count(e.id) + count(k.id))/case when (count(el.id) + count(ek.id) + count(kl.id) + count(kk.id)) = 0 
then 1 else (count(el.id) + count(ek.id) + count(kl.id) + count(kk.id)) end) as kohaloleku_prots,
count(bk.id) as mitte_kasutatud_broon, 0 as hind, 0 as progn_kaive, 0 as korreg_kaive 
from visiidid v left join erialad e on v.eriala_kood = e.kood 
left join(select * from visiidid where tyyp_kood = 'V1' and lisanumber is null) e on v.id = e.id 
left join (select * from visiidid where tyyp_kood = 'V2' and lisanumber is null) k on v.id = k.id 
left join (select * from visiidid where tyyp_kood = 'V1' and lisanumber = 'J' and kohal = 'J') el on v.id = el.id 
left join (select * from visiidid where tyyp_kood = 'V2' and lisanumber = 'J') kl on v.id = kl.id 
left join (select * from visiidid where tyyp_kood = 'V1' and kohal = 'J' and lisanumber is null) ek on v.id = ek.id 
left join (select * from visiidid where tyyp_kood = 'V2' and kohal = 'J' and lisanumber is null) kk on v.id = kk.id 
left join (select * from visiidid where tyyp_kood 
in ('V1', 'V2') and lisanumber is not null and kohal is null) bk on v.id = bk.id 
WHERE v.algus BETWEEN TO_DATE('2006-05-01', 'YYYY-MM-DD') AND TO_DATE('2006-05-24', 'YYYY-MM-DD') 
and v.staatus not in ('P', 'A') and asukoht_id IN (1,81) GROUP BY e.eriala_nimetus

PARTITION BY

select count(*),
       sum( case when deptno = 10 THEN 1 ELSE 0 END) dept_10,
       sum( case when deptno = 20 THEN 1 ELSE 0 END) dept_20,
       sum( case when deptno = 30 THEN 1 ELSE 0 END) dept_30
from emp

14 3 5 6