Hi all,
The union all shows high cost in the explain plan.
This is query:
with inflation as (
select *
from (
select re.req_id, fy, rate * 0.01 + 1 as inf, lkp_inflation_rate_type_id
from lkp_inflation_rates
join lkp_inflation_rate_base using(lkp_inflation_rate_base_id)
join lkp_inflation_rate_type using (lkp_inflation_rate_type_id)
join requirement re on re.req_id = lkp_inflation_rate_base.req_id
where lkp_inflation_rates.fy = re.fydp_startyear + 2
and re.req_status_id = 1
)
pivot ( sum(inf) for lkp_inflation_rate_type_id in (2 as inf_index ,20 as fuel_inf_index) )
order by fy
)
,rasData as (
select re.req_id_alias || ' - ' || re.req_desc as req_desc, resource_sponsor_name, unit_program_name, unit_type_name, pillar, appn, bso_id, pe, li_id, si_name, civper, t.fy,
sum(nvl(case when t.fydp_startyear = t.currentpom then exec_approved else exec_approved * case when acct_l = 'FUEL' then i.fuel_inf_index else i.inf_index end end,0)) as amt
from vw_rpt_ras t
join requirement re on re.req_id = t.req_id
left join inflation i on i.fy = t.fy
where ras_expired = 'ACTIVE'
and ras_status in (2,3)
and t.fy = re.fydp_startyear + 2
and re.req_status_id = 1
group by re.req_id_alias || ' - ' || re.req_desc, resource_sponsor_name, unit_program_name, unit_type_name, pillar, appn, bso_id, pe, li_id, si_name, civper, t.fy
)
,scData as (
select re.req_id_alias || ' - ' || re.req_desc as req_desc, resource_sponsor_name, unit_program_name, unit_type_name, pillar, appn, bso as bso_id, v.pe, li as li_id, si_name, civper, min(v.fy) as fy,
sum(costs)/5 * 0.96 as amt
from vw_rpt_scr_adjusted v
join program_element pe on pe.pe = v.pe
join resource_sponsor rs on rs.resource_sponsor_id = pe.resource_sponsor_id
join requirement re on re.req_id = v.req_id
where fy between re.fydp_startyear + 2 and re.fydp_endyear
and re.req_status_id = 1
group by re.req_id_alias || ' - ' || re.req_desc, resource_sponsor_name, unit_program_name, unit_type_name, pillar, appn, bso, v.pe, li, si_name, civper
)
select req_desc, resource_sponsor_name, unit_program_name, unit_type_name, pillar, appn, bso_id, pe, li_id, si_name, civper, fy, sum(amt) as amt
from(
select req_desc, resource_sponsor_name, unit_program_name, unit_type_name, pillar, appn, bso_id, pe, li_id, si_name, civper, fy, amt
from rasData
union all
select req_desc, resource_sponsor_name, unit_program_name, unit_type_name, pillar, appn, bso_id, pe, li_id, si_name, civper, fy, amt
from scData
)
group by req_desc, resource_sponsor_name, unit_program_name, unit_type_name, pillar, appn, bso_id, pe, li_id, si_name, civper, fy
Is there a better way to join the data without "union all" for better performance?
Thanks in advance.
JFB