ALTER MATERIALIZED VIEW

The ALTER MATERIALIZED VIEW statement is used to modify an existing materialized view. A materialized view is a database object that contains the results of a query. The ALTER MATERIALIZED VIEW statement can rename a materialized view, change the name of a column within the view, set the freshness mode, and add or drop indexes. Note that this statement does not allow for changing the query that defines the materialized view.

Syntax

In Backus-Naur Form (BNF), the ALTER MATERIALIZED VIEW statement can be represented as follows:

<alter_materialized_view> ::= ALTER MATERIALIZED VIEW <view_name> <alter_materialized_view_action>

<view_name> ::= <identifier>

<alter_materialized_view_action> ::= 
    <rename_to_clause> | 
    <rename_column_clause> | 
    <set_freshness_clause> |
    <add_index_clause> |
    <drop_index_clause> |
    <refresh_clause>

<rename_to_clause> ::= RENAME TO <new_view_name>

<new_view_name> ::= <identifier>

<rename_column_clause> ::= RENAME COLUMN <column_name> TO <new_column_name>

<column_name> ::= <identifier>

<new_column_name> ::= <identifier>

<set_freshness_clause> ::= SET FRESHNESS MODE <freshness_mode>

<freshness_mode> ::= INTERVAL <time_value> <freshness_id> | UPDATE <time_value> | MANUAL

<add_index_clause> ::= ADD [UNIQUE] INDEX <index_name> ON ( <column_list> ) [USING <index_method>] [ON STORE <store_name>]

<index_name> ::= <identifier>

<column_list> ::= <identifier> [, <identifier> ...]

<index_method> ::= <identifier>

<store_name> ::= <identifier>

<drop_index_clause> ::= DROP INDEX <index_name>

<refresh_clause> ::= REFRESH

In this representation:

• <identifier> is any valid identifier name in your database system.
• [...] means the content inside is optional.
• ( ... ) is used to group items.
• | separates alternatives.
• , is a literal comma.
• The syntax parts <rename_to_clause>, <rename_column_clause>, <freshness_clause>, <add_index_clause>, and <drop_index_clause> represent different actions that can be performed with the ALTER MATERIALIZED VIEW statement.

Renaming a Materialized View

If you want to change the name of a materialized view, you can use the ALTER MATERIALIZED VIEW statement along with the RENAME TO clause.

Syntax

The syntax for renaming a materialized view is:

ALTER MATERIALIZED VIEW current_view_name RENAME TO new_view_name;

In this syntax, current_view_name is the name of the existing materialized view that you wish to rename. new_view_name is the new name that you want to assign to the materialized view.

Please note:

• You need the necessary privileges to rename a materialized view.
• The new view name should not conflict with an existing table, view, or materialized view name in the same namespace.
• After renaming the materialized view, any queries that refer to the old view name must be updated to use the new name.

Example

Here is an example of how to rename a materialized view:

ALTER MATERIALIZED VIEW old_view_name RENAME TO new_view_name;

In this example, the materialized view named ‘old_view_name’ would be renamed to ‘new_view_name’.

Renaming a Column in a Materialized View

The ALTER MATERIALIZED VIEW statement along with the RENAME COLUMN clause allows you to rename a column in a materialized view.

Syntax

The syntax to rename a column in a materialized view is:

ALTER MATERIALIZED VIEW view_name RENAME COLUMN current_column_name TO new_column_name;

In this syntax, view_name refers to the name of the materialized view whose column you wish to rename. current_column_name and new_column_name represent the existing and new names of the column, respectively.

Please note:

• You need the necessary privileges to rename a column in a materialized view.
• The new column name should not conflict with other column names in the same materialized view.
• After renaming the column, any queries that refer to the old column name must be updated to use the new name.

Example

Here is an example of how to rename a column in a materialized view:

ALTER MATERIALIZED VIEW my_view RENAME COLUMN old_column TO new_column;

In this example, the column named ‘old_column’ in the materialized view ‘my_view’ would be renamed to ‘new_column’.

Adjusting the Freshness Mode of a Materialized View

The ALTER MATERIALIZED VIEW statement can be used to change the freshness mode of a materialized view. Freshness modes determine when and how often a materialized view is refreshed to keep its data up-to-date.

Syntax

The syntax to set the freshness mode of a materialized view is:

ALTER MATERIALIZED VIEW view_name SET FRESHNESS MODE ( INTERVAL time | UPDATE time | MANUAL);

In this syntax, view_name refers to the name of the materialized view that you wish to modify, and time is a time interval in seconds.

Please note:

• You need the necessary privileges to alter the freshness mode of a materialized view.
• Remember that refreshing materialized views can be a resource-intensive operation. Choose a freshness mode that is appropriate for your application’s performance requirements and the frequency of updates to your data.

Freshness Modes

There are three freshness modes you can choose from:

1. Interval mode: The materialized view is refreshed automatically at a fixed interval of time. The time is specified in seconds.
2. Update mode: The materialized view is refreshed automatically after a fixed number of updates to the underlying base table(s). The number of updates is specified in the command.
3. Manual mode: The materialized view is only refreshed manually when a REFRESH MATERIALIZED VIEW command is issued.

Examples

Here are examples of how to set the freshness mode of a materialized view:

To set the freshness mode to interval:

ALTER MATERIALIZED VIEW my_view SET FRESHNESS MODE INTERVAL 3600;

In this example, the materialized view named ‘my_view’ will be refreshed every 3600 seconds (1 hour).

To set the freshness mode to update:

ALTER MATERIALIZED VIEW my_view SET FRESHNESS MODE UPDATE 100;

In this example, the materialized view named ‘my_view’ will be refreshed after every 100 updates to the underlying base table(s).

To set the freshness mode to manual:

ALTER MATERIALIZED VIEW my_view SET FRESHNESS MODE MANUAL;

In this example, the freshness mode of the materialized view named ‘my_view’ is set to manual. This means that the view will not be refreshed with updated data unless the REFRESH MATERIALIZED VIEW command is explicitly issued.

Adding an Index to a Materialized View

Indexes can improve the speed of data retrieval operations on a database by providing swift access to rows in a materialized view. The ADD INDEX clause is used to add a new index.

Syntax

The syntax to add an index to a materialized view is:

ALTER MATERIALIZED VIEW view_name ADD [UNIQUE] INDEX index_name ON (column_name_list) [USING index_method] [ON STORE store_name];

In this syntax, view_name refers to the name of the materialized view to which you want to add an index.

• UNIQUE is an optional keyword indicating that the new index will enforce a unique constraint.
• index_name is the name you want to give to the new index.
• column_name_list is a comma-separated list of columns to be included in the index.
• index_method is an optional specification of the method used to store the index. If not specified, the system will use the default index method.
• store_name is an optional name of the store where the index will be placed. If not provided, Polypheny will decide the best place to store the index. This can either be on the level of Polypheny itself or on one or multiple of the underlying data stores.

Please note:

• You need the necessary privileges to add an index to a materialized view.
• Adding an index can take some time if the materialized view is large, as it involves scanning the entire materialized view and building the index data structure.
• The index method and store may have performance implications depending on the specifics of your data and query workload. Make sure to choose them carefully.

Example

Here is an example of how to add an index to a materialized view:

ALTER MATERIALIZED VIEW my_view ADD INDEX idx_name ON (column1, column2) USING btree ON STORE my_store;

In this example, an index named ‘idx_name’ is added to the materialized view named ‘my_view’. The index is built on ‘column1’ and ‘column2’ using the B-tree index method and is stored on ‘my_store’.

Dropping an Index from a Materialized View

The DROP INDEX clause is used to drop an existing index. Dropping an index can help save storage space and improve write performance in some cases.

Syntax

The syntax to drop an index from a materialized view is:

ALTER MATERIALIZED VIEW view_name DROP INDEX index_name;

In this syntax, view_name refers to the name of the materialized view from which you want to drop an index, and index_name is the name of the index that you want to drop.

Please note:

• You need the necessary privileges to drop an index from a materialized view.
• Be careful when dropping an index. If queries on the materialized view rely on the index for speed, their performance may be degraded after the index is dropped.
• Dropping an index does not automatically remove the data in the index from disk. The storage engine takes care of this operation, which may occur at a later time. Therefore, the disk space might not be immediately reclaimed after dropping an index.

Example

Here is an example of how to drop an index from a materialized view:

ALTER MATERIALIZED VIEW my_view DROP INDEX idx_name;

In this example, the index named ‘idx_name’ is dropped from the materialized view named ‘my_view’.

Refreshing a Materialized View

This statement can be used to manually refresh a materialized view. Refreshing a materialized view means re-running the query that defines the materialized view and updating the materialized view with the result. This is useful when the data in the underlying tables has changed and you want the materialized view to reflect the changes.

Syntax

The syntax to manually refresh a materialized view is:

ALTER MATERIALIZED VIEW view_name REFRESH;

In this syntax, view_name refers to the name of the materialized view that you want to refresh.

Please note:

• You need the necessary privileges to refresh a materialized view.
• Refreshing a materialized view can take some time if the materialized view is large or if the query that defines the materialized view is complex.
• Keep in mind that while a materialized view is refreshing, it can’t be queried. Therefore, it’s best to refresh materialized views during periods of low usage.
• In general, it’s best to rely on the materialized view’s automatic refresh functionality, unless you specifically need the materialized view to reflect recent changes to the underlying tables immediately.

Example

Here is an example of how to manually refresh a materialized view:

ALTER MATERIALIZED VIEW my_view REFRESH;

In this example, the materialized view named ‘my_view’ is refreshed.

Previous

UPSERT