Caching sovccur order field

The order field, sovccur_order, in the Ellucian Banner sovccur view is dynamically generated (via a function). So it can be slow under some conditions. Materializing the value, if the function is performing slowly, can be a challenge. In my case, I needed to compare a new column to the order column. A simple query never finished after 7 hours. So, dumping the order column to a table seemed like the next step. But doing so using a simple query doesn't work.

This method does, however. Simply update the view to retrieve a year at a time.

create or replace view t_sovccur_vw as
with
cte_sov as (
select
 *
from sovccur
where 1=1
and substr(sovccur_term_code,1,4) in ( '2004' )
and sovccur_lmod_code = 'LEARNER'
)
select
 sovccur_pidm contactid
 ,sovccur_seqno seq_num
 ,sovccur_lmod_code module_code
 ,sovccur_term_code academic_period
 ,sovccur_order order_num
from cte_sov
;
select academic_period, count(*) from t_sovccur_vw group by academic_period; -- fast
insert into t_sovccur select * from t_sovccur_vw;
commit;