documentation/query/sql/alter-mat-view-set-refresh.md
Changes a materialized view's refresh strategy and parameters without recreating the view.
ALTER MATERIALIZED VIEW viewName SET REFRESH
[ IMMEDIATE | MANUAL | EVERY interval [ START timestamp ] [ TIME ZONE timezone ] ]
[ PERIOD ( LENGTH length [ TIME ZONE timezone ] [ DELAY delay ] ) ]
[ PERIOD ( SAMPLE BY INTERVAL ) ]
| Parameter | Description |
|---|---|
viewName | Name of the materialized view to modify |
IMMEDIATE | Refresh after each base table transaction |
MANUAL | Refresh only when explicitly triggered |
EVERY interval | Refresh on a timer (e.g., 10m, 1h, 1d) |
START timestamp | When to begin the timer schedule |
TIME ZONE | Timezone for schedule alignment |
PERIOD LENGTH | Define fixed-length refresh periods |
PERIOD SAMPLE BY INTERVAL | Match period to the view's SAMPLE BY interval |
DELAY | Grace period before period closes |
Change refresh strategy when:
IMMEDIATE) to batched (EVERY) for performanceMANUAL for full control during maintenance windowsALTER MATERIALIZED VIEW trades_hourly
SET REFRESH EVERY 12h START '2025-12-31T00:00:00Z' TIME ZONE 'Europe/London';
ALTER MATERIALIZED VIEW trades_daily
SET REFRESH PERIOD (LENGTH 1d DELAY 1h);
ALTER MATERIALIZED VIEW trades_hourly
SET REFRESH PERIOD (SAMPLE BY INTERVAL);
ALTER MATERIALIZED VIEW trades_hourly SET REFRESH IMMEDIATE;
ALTER MATERIALIZED VIEW trades_hourly SET REFRESH MANUAL;
| Aspect | Description |
|---|---|
| Existing data | Preserved; only future refresh behavior changes |
| Pending refresh | Completes before new strategy takes effect |
| Timer reset | EVERY schedule resets based on START time |
Changing refresh settings requires the ALTER MATERIALIZED VIEW permission:
GRANT ALTER MATERIALIZED VIEW ON trades_hourly TO user1;
| Error | Cause |
|---|---|
materialized view does not exist | View with specified name doesn't exist |
invalid interval | Timer interval is invalid or below minimum (1m) |
permission denied | Missing ALTER MATERIALIZED VIEW permission (Enterprise) |