I believe that such query will return the next run date you require:
select least(TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH'),
next_day(sysdate, 'Sunday')) as answer from dual;
It calculates dates of next Sunday and 1st of next month and then returns the one which will be sooner.
Just 'Sunday' in next_day is NLS dependent.
That should give your required interval in call to DBMS_REFRESH.MAKE procedure:
interval=>'least(TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH'), next_day(sysdate, 'Sunday'))'
Just use DBMS_MVIEW.EXPLAIN_MVIEW
+ MV_CAPABILITIES_TABLE
to find why fast refresh is not possible on your mview.
Fast refresh is possible without a join condition.
Create the table for storing explain results:
SQL> @?/rdbms/admin/utlxmv
Table created.
Create tables + mview:
create table t1 (c1 number primary key, c2 varchar2(20 char));
create table t2 (c3 number primary key, c4 varchar2(20 char));
create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;
create materialized view mv1
refresh fast on commit
as select t1.c1, t1.c2, t2.c3, t2.c4,
t1.rowid as rt1, t2.rowid as rt2 from t1, t2;
Explain mview and see fast refresh capabilities:
SQL> exec dbms_mview.explain_mview('MV1');
PL/SQL procedure successfully completed.
SQL> select capability_name, possible, msgtxt from mv_capabilities_table
where capability_name like 'REFRESH_FAST%'
CAPABILITY_NAME P MSGTXT
---------------------------------------- - ----------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail
tables in the materialized view
PCT fast refresh is not possible because there are no partitions in this example.
Verify fast refresh:
SQL> select * from mv1;
no rows selected
SQL> insert into t1 select rownum, 'A' from dual connect by level <= 2;
2 rows created.
SQL> insert into t2 select rownum, 'A' from dual connect by level <= 2;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from mv1;
C1 C2 C3 C4 RT1 RT2
---------- -------------------- ---------- -------------------- ------------------ ------------------
1 A 1 A AAAOOLAAEAAAACPAAA AAAOONAAEAAAACvAAA
1 A 2 A AAAOOLAAEAAAACPAAA AAAOONAAEAAAACvAAB
2 A 1 A AAAOOLAAEAAAACPAAB AAAOONAAEAAAACvAAA
2 A 2 A AAAOOLAAEAAAACPAAB AAAOONAAEAAAACvAAB
SQL> select last_refresh_type from dba_mviews where mview_name = 'MV1';
LAST_REF
--------
FAST
Best Answer
The
BUILD DEFERRED
clause in theCREATE MATERIALIZED VIEW
DDL statement causes the actual population to be deferred until the first refresh.While Oracle parses the actual SQL used to populate the view, it does not execute it & will therefore not pick up "runtime" problems.
This is easily demonstrated.
Parsing error, due to
NONSENSE
not being a datatype:Runtime error, that won't get picked up as it doesn't analyse the actual values involved, and is unaware that the included string isn't a number: