Postgresql – Postgres Materialized View Dramatically Increasing In Size

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'))'

No. How would the query processor recognize that it should use the materialized view. The materialized view is another object.

See http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html

In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view."

So materialized views are useful, but you need to program not only for their maintenance, but also doing the necessary coding to use the materialized view.