Materialized view support in different DBMS

Some notes regarding materialized view support in different database management systems.

Materialized view is a database object that stores a snapshot of database query.

If a regular database view could be seen as a “window” – one looks through this window and sees actual situation of the data, then “materialized view ” would be something like a “photo image” i.e., you take an image and can look at it even without going near window, but you only see situation that was on that particular moment picture was taken and if you want to update – you have to take another picture etc.

Different DBMS have different materialized view implementation, lets go thorough most popular ones and look how they are supported.

Oracle

Wikipedia states that oracle was first to implement materialized view in version 8i.

CREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
   NEXT SYSDATE + 1
     AS SELECT * FROM <table_name>;

PostgreSQL

PostgreSQL supports materialized view from version 9.3

CREATE MATERIALIZED VIEW MV_MY_VIEW
 [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
     AS SELECT * FROM <table_name>;

SQL Server

SQL server has different concept and it is called Indexed View. The main difference is that such views do not require a refresh because they are in fact always synchronized to the original data of the tables that compound the view. To achieve this, it is necessary that the lines of origin and destination are “deterministic” in their mapping which limits the types of possible queries to do this.

CREATE VIEW MV_MY_VIEW
WITH SCHEMABINDING
AS 
SELECT COL1, SUM(COL2) AS TOTAL
FROM <table_name>
GROUP BY COL1;
GO
CREATE UNIQUE CLUSTERED INDEX XV 
   ON MV_MY_VIEW (COL1);

MariaDB or MySQL

No native materialized view support in MariaDB or MySQL. Can be implemented using FlexiViews.