Good morning,
Using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Posting another view that takes 35 secs, and need some performance improvement.
with all_data as (
select v.req_id, ras_type_name, unit_program_name, unit_type_name, oco, pillar, appn, bso as bso_id, pe, q.ppbe_id,
li as li_id, si_name, case when (q.fydp_startyear + 2) > 2024 then 'N' else civper end civper, q.fydp_startyear + 2 as currentpom,
ras_status, cto, ctt, cte, fy, sum(costs) costs,
dense_rank() over(partition by oco, pillar, appn, bso order by fy) rnk
from vw_rpt_scr_adjusted v
join requirement q on q.req_id = v.req_id
where fy between 2027 and 2031 --5 years
group by v.req_id, ras_type_name, unit_program_name, unit_type_name, oco, pillar, appn, bso, pe, q.ppbe_id,
li, si_name, case when (q.fydp_startyear + 2) > 2024 then 'N' else civper end, q.fydp_startyear,
ras_status, cto, ctt, cte, fy
)
, scr_data as (
select req_id, ras_type_name, r.resource_sponsor_name, s.unit_program_name, unit_type_name, oco, pillar, s.appn, s.bso_id, s.pe,
s.li_id, s.si_name, s.civper, ras_status, cto, ctt, cte, fy, rnk, costs
from all_data s
join vw_get_rs r on r.pe = s.pe
and (
(currentpom < 2023 and rs_table = 'OLD')
or (currentpom >= 2023 and rs_table = 'NEW'
and r.unit_program_name = s.unit_program_name
and r.ppbe_id = s.ppbe_id
and r.appn = s.appn
and r.bso_id = s.bso_id
and r.li_id = s.li_id
and r.si_name = s.si_name
and r.civper = s.civper)
)
)
select req_id, ras_type_name, resource_sponsor_name, unit_program_name, unit_type_name, oco, pillar, appn, bso_id, pe,
li_id, si_name, civper, ras_status
,(avg_all) * 0.96 as fy_1
,(avg_all) * 0.98 as fy_2
,(avg_all) * 1 as fy_3
,(avg_all) * 1.02 as fy_4
,(avg_all) * 1.04 as fy_5
,( decode(fy_3, 0, 0, cto / fy_3 * 100)) as cto
,( decode(fy_3, 0, 0, ctt / fy_3 * 100)) as ctt
,( decode(fy_3, 0, 0, cte / fy_3 * 100)) as cte
from (
select req_id, ras_type_name, resource_sponsor_name, unit_program_name, unit_type_name, oco, pillar, appn, bso_id, pe,
li_id, si_name, civper, ras_status
--,sum(case when rnk = 1 then nvl(costs, 0) else 0 end) as fy_1
--,sum(case when rnk = 2 then nvl(costs, 0) else 0 end) as fy_2
,sum(case when rnk = 3 then nvl(costs, 0) else 0 end) as fy_3
--,sum(case when rnk = 4 then nvl(costs, 0) else 0 end) as fy_4
--,sum(case when rnk = 5 then nvl(costs, 0) else 0 end) as fy_5
,sum(costs)/5 avg_all
,sum(case when rnk = 3 then cto * costs * 0.01 else 0 end) as cto
,sum(case when rnk = 3 then ctt * costs * 0.01 else 0 end) as ctt
,sum(case when rnk = 3 then cte * costs * 0.01 else 0 end) as cte
from scr_data
group by req_id, ras_type_name, resource_sponsor_name, unit_program_name, unit_type_name, oco, pillar, appn, bso_id, pe,
li_id, si_name, civper, ras_status
)
--group by req_id, ras_type_name, resource_sponsor_name, unit_program_name, unit_type_name, oco, pillar, appn, bso_id, pe,
--li_id, si_name, civper, ras_status
I fixed the first grouping (all_data), remove some extra years and remove the last group by.
The plan is large, doing attachment.
Is there a way to improve the query?
Thanks, Johnny
plan2.txt