...
- Banner Replicant MViews created with re-created Banner indexes.
- Rowid-based view logs created on Replicant Mviews.
Critical Refresh Mviews
The BANNER_INSTRUCTORS and BANNER_ENROLLMENT mviews need to have the fastest refresh period possible. In order to achieve this, these mviews will need to be FAST REFRESH capable. The ddl for the two mviews is listed below, for purposes of review and optimization:
| Code Block |
|---|
|
create materialized view BANNER_ENROLLMENT ( ENROLLMENTSET_EID, PIDM, ENROLLMENTSTATUS, DROPPED, CREDITS, GRADINGSCHEME,
CREATEDBY, CREATEDDATE, LASTMODIFIEDBY, LASTMODIFIEDDATE )
REFRESH FORCE
NEXT sysdate + 1
as
select 'http://ucdavis.edu/course/enrollmentset/'||c1.SFRSTCR_CRN||'/'||c1.SFRSTCR_TERM_CODE,
c1.SFRSTCR_PIDM, c1.SFRSTCR_RSTS_CODE, CASE WHEN c1.SFRSTCR_RSTS_CODE IN ('RE','WL') THEN 0 ELSE 1 END,
c1.SFRSTCR_CREDIT_HR, c1.SFRSTCR_GMOD_CODE,
'BANNER', c1.SFRSTCR_ACTIVITY_DATE, 'BANNER', c1.SFRSTCR_ACTIVITY_DATE
from SFRSTCR c1
where c1.SFRSTCR_TERM_CODE > 200410
and c1.SFRSTCR_TERM_CODE <> 999999;
|
| Code Block |
|---|
|
create materialized view BANNER_INSTRUCTORS ( ENROLLMENTSET_EID, SECTION_EID, PIDM )
REFRESH FORCE
NEXT sysdate + 1
as
select distinct 'http://ucdavis.edu/course/enrollmentset/'||sa.SIRASGN_CRN||'/'||sa.SIRASGN_TERM_CODE,
'http://ucdavis.edu/course/section/'||sb.SSBSECT_SUBJ_CODE||'/'||sb.SSBSECT_CRSE_NUMB||'/'||sb.SSBSECT_SEQ_NUMB||'/'||sb.SSBSECT_TERM_CODE,
sa.SIRASGN_PIDM
from SIRASGN sa, SSBSECT sb
where sa.SIRASGN_TERM_CODE > 200410
and sa.SIRASGN_TERM_CODE <> 999999
and sa.SIRASGN_TERM_CODE = sb.SSBSECT_TERM_CODE
and sa.SIRASGN_CRN = sb.SSBSECT_CRN;
|