ALTER MATERIALIZED VIEW - Kinetica Docs

Alters the configuration of a materialized view. Any of the following facets of a materialized view can be altered:

Move View

A materialized view can be moved from one schema to another.

Move View Syntax

ALTER MATERIALIZED VIEW [<schema name>.]<view name>
< MOVE TO | SET SCHEMA > <new schema name>

All dependent views, materialized views, streams, and SQL procedures will be dropped. For example, to move the sales_current view from the example_olap schema to the example_archive schema:

Move Materialized View Example

ALTER MATERIALIZED VIEW example_olap.sales_current
MOVE TO example_archive

Set Access Mode

A materialized view can have its global accessibility modified for all users in the system, independently from and further restricting any role-based access controls in place.

Set Materialized View Access Mode Syntax

ALTER MATERIALIZED VIEW [<schema name>.]<view name>
SET ACCESS MODE < NO_ACCESS | READ_ONLY | WRITE_ONLY | READ_WRITE >

Changing the access mode cannot widen access for users not already granted access; it can only narrow access for those who already have access. This setting will also trump administrative access to a materialized view.

Set Execution User

A materialized view can have its execution user for periodic refreshes (REFRESH is set to EVERY ...) changed to the given user.

Set Materialized View Execution User Syntax

ALTER MATERIALIZED VIEW [<schema name>.]<view name>
SET EXECUTE AS '<user name>'

If this user doesn’t exist at the time of a refresh, the refresh will be executed as the creating user, and, failing that, the system administration user.

Set Refresh Mode

The refresh mode of a materialized view can be modified.

Set Materialized View Refresh Mode Syntax

ALTER MATERIALIZED VIEW [<schema name>.]<view name>
SET REFRESH
<
    OFF |
    ON CHANGE |
    ON QUERY |
    EVERY <number> <SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]>
        [STARTING AT '<YYYY-MM-DD [HH:MM[:SS]]>']
        [STOP AFTER < '<YYYY-MM-DD [HH:MM[:SS]]>' | <number> <SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]> >]
>

The available refresh modes are:

Constant	Description
`OFF`	Will prevent the materialized view from being automatically refreshed, but will still allow manual refreshes of the data to be requested
`ON CHANGE`	Will cause the materialized view to be updated any time a record is added, modified, or deleted from the subtending tables in its query
`ON QUERY`	Will cause the materialized view to be updated any time it is queried
`EVERY`	Allows specification of an interval in seconds, minutes, hours, or days, at which the materialized view should be refreshed. By default, the first refresh interval will be one interval’s worth of time from the point at which the materialized view alteration was requested. This can be modified with the following options: * `STARTING AT`: specify a date or timestamp at which refresh cycles should begin * `STOP AFTER`: specify a date, timestamp, or time interval after which refresh cycles should end

For example, to alter the current sales materialized view to refresh every 6 hours:

Set Materialized View Refresh Mode by Hour Example

ALTER MATERIALIZED VIEW example_olap.sales_current
SET REFRESH EVERY 6 HOURS

Set Materialized View Refresh Mode with Start/Stop Example

ALTER MATERIALIZED VIEW example_olap.sales_current
SET REFRESH EVERY 1 MINUTE
    STARTING AT '2026-01-01 00:00:00'
    STOP AFTER 31 DAYS

Set TTL

A materialized view’s time-to-live (TTL) can be altered.

Set Materialized View TTL Syntax

ALTER MATERIALIZED VIEW [<schema name>.]<view name>
SET TTL <new ttl>

​Move View

​Set Access Mode

​Set Execution User

​Set Refresh Mode

​Set TTL

Move View

Set Access Mode

Set Execution User

Set Refresh Mode

Set TTL