A materialized view is a physical copy of a base table or the result of a query that is stored in the database. Unlike views, the data of a materialized views is physically stored in one or multiple storage engines. It is updated based on a specified schedule or event. This makes materialized views a powerful tool for optimizing complex queries and computations.
The CREATE MATERIALIZED VIEW statement is used to create a materialized view.
Syntax
The basic syntax for a CREATE MATERIALIZED VIEW statement in Polypheny is:
CREATE MATERIALIZED VIEW view_name [ (column1, column2, ... ) ]
AS query
[ON STORE store_name [, store_name ...]]
[FRESHNESS {INTERVAL time identifier | UPDATE time | MANUAL}]
Parameters
view_name: Specifies the name of the materialized view. The name must be unique within the namespace.column1, column2, ...: Optional list of names for the columns in the view. If provided, the number of names must match the number of columns returned by thequery.query: An SQL query that defines the view. This can be any valid SELECT statement that retrieves data from tables in the database.store_name: Optional name(s) of the store(s) where the materialized view should be placed. If not specified, Polypheny will decide on the placement.FRESHNESS: Defines how often the materialized view is refreshed.INTERVAL time identifier: Specifies that the materialized view should be refreshed at regular intervals defined by thetimeandidentifier. Theidentifiercan be SECOND, MINUTE, HOUR, DAY.UPDATE time: Specifies that the materialized view should be refreshed after a specified number (time) of updates.MANUAL: Specifies that the materialized view should only be refreshed manually.
Example
Here is an example of a CREATE MATERIALIZED VIEW statement:
CREATE MATERIALIZED VIEW employee_mv AS
SELECT first_name, last_name
FROM employees
WHERE salary > 50000
ON STORE store1, store2
FRESHNESS INTERVAL 5 HOUR
In this example, a materialized view named employee_mv is created, which presents the first name and last name of employees whose salary exceeds 50000. The materialized view is stored on store1 and store2 and is refreshed every 5 hours.
