On this page

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.


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}]


  • 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 the query.
  • 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 the time and identifier. The identifier can 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.


Here is an example of a CREATE MATERIALIZED VIEW statement:

SELECT first_name, last_name
FROM employees
WHERE salary > 50000
ON STORE store1, store2

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.

Unlike a standard view, which is not physically materialized in the database and is generated on-the-fly every time you query the view, a materialized view is stored physically. This means that the data does not always reflect the most up-to-date state of the underlying tables. The data in a materialized view is updated based on the specified freshness parameter. This can significantly speed up query performance but comes at the cost of potentially displaying outdated information.
© Polypheny GmbH. All Rights Reserved.