Materialized View vs. Tables: What are the advantages?

Question

It's clear to me why a materialized view is preferable over just querying a base table. What is not so clear is the advantage over just creating another table with the same data as the MV. Is the only advantage to the MV really just the ease of creation/maintenance?

Isn't an MV equivalent to a table with matching schema and an INSERT INTO using the MVs SELECT statement?

Meaning, you can create an MV as follows:

CREATE MATERIALIZED VIEW ... AS
SELECT * FROM FOO;

And you can create an equivalent table:

CREATE TABLE bar (....);
INSERT INTO bar 
SELECT * FROM FOO;

Not to say that ease of creation/maintenance isn't enough of an advantage, I just want to make sure I'm not missing anything.

Borneo

CREATE VIEW does not create a Materialized View. – Ringworm

Well, to be precise this doesn't create a Materialized View, but in SQL Server and PostgreSQL it doesn't preclude a Materialized View either. – Borneo

I would change the "Tables" with "Rollup Tables" in the question title. Like here: citusdata.com/blog/2018/10/31/… – Unreligious

Answer 1

Dynamic query rewriting. Materialized views define not only relationships, but also allow you to precompute expensive joins and aggregations. The optimizer is smart enough to use the MV to fetch relevant data even if the MV isn't explicitly used in the query (given DB settings, etc).

Your question was tagged as Oracle, but MSSQL also does similar tricks.

Hodeida

Answer 2

They're basically equivalent, but the MV has various options for automatically refreshing the data, which not only improve ease of maintenance but also, in some cases, efficiency, since it can track changes by row.

Aforementioned

Answer 3

Materialized views can be refreshed - they are snapshots of data taken at regular intervals.

Your second statement is just a one time deal - data gets inserted into Table at that moment. Further changes to the original data do not get reflected in the table.

Mintz

Answer 4

1. The materialized view will stay synchronized with the base relations on which it depends.

2. If the materialized view is updatable, when you modify the materialized view, it will also modify the base relation on which it depends.

Fabricate

Are they really sync? or we should refresh them for latest changes? – Mello

At least in Postgres it's not automatically synced. – Kitty

Answer 5

the big advantage of a Materialized View is extremely fast retrieval of aggregate data, since it is precomputed and stored, at the expense of insert/update/delete. The database will keep the Materialized View in sync with the real data, no need to re-invent the wheel, let the database do it for you.

Mutter

Answer 6

1) Speeding up write operations: Since indexes can be created on materialized views, reading from them is very fast. Note that if you create an index on a table that includes a lot of writes, index maintenance overhead tends to slow down the write process. To avoid this you can create a materialize view and create indexes on them. These indexes can be maintained in the background and does not adversely affect table write operations.

2) Speeding read operations: Complex joins; pivots that take ages to run can be speed up by creating indexes on the materialized views. This becomes very handy in most reporting scenarios.

Countertype

Answer 7

I guess the correct comparison would be:

REFRESH MATERIALIZED VIEW bar;

versus:

CREATE TABLE bar (....);
INSERT INTO bar 
SELECT * FROM FOO;

Because the MV you can make it once, and refresh when you need to make the select (and even spare some calls if you know how oft the info changes)

Also you can provide and index to the MV, and that's something that you don't have the other way. Of course that would favor the performance of MV only for big result sets.

In postgres you can do also this:

REFRESH MATERIALIZED VIEW CONCURRENTLY bar;

to refresh the info by a parallel process without blocking the queries that need the current info. I guess that some optimization is done to reuse stuff from the running query.

That's something you can not do with SELECT INSERT INTO.

Unreligious

Answer 8

In additition to the already mentionned advantages:

• dynamic query rewriting (in short, the DB optimizer knows how the MV is created, so it can reuse it to optimize other queries),
• optional, automatic, possibly incremental refresh,

I'd like to mention:

• some materialized views can be written to, which updates the source table (for instance joins with primary keys can be written to, on the opposite if the materialized view is the result of a group by it can't be written to)
• the DB server retains the query that created the data and can rerun it. If you create a table, you need an external tool (possibly just a custom script) to rerun the query whenever a refresh is needed / asked by the user. (I work for a company developing a tool that does that and much more).

Desalvo

Answer 9

In addition to the other answers (because I haven't seen it), I would say that although they both use up space, the materialized view is logically normalized, whereas the extra table is logically denormalized. If this is something that is not a temporary one-off, you will have to remember to update the second table whenever you update the base table.

Undis

Answer 10

Materialize views are in fact best choice over tables where aggregations are required regularly to show updated result sets. We can use Materialized view other than Data ware housing in Inventory modules for calculating daily, weekly, monthly stock with closing balance rather than using complex queries every time , we can make materialized views to fetch such results in no time.

Whitewing

do you face any performance issue when u implement materialized view for calculating daily, weekly, monthly stock? – Surpassing

Answer 11

The difference between table and MV is with table , you can do DML operations which will be seen by other users whereas the changes you do to MV will not be available to others until you update your database server.

MV has another advantage when you build MV based on multiple tables using complex queries, the users when using MV the performance increases drastically.

Declass

The first point doesnt sound like an advantage. Also it seems like it is copied from here without any reference itknowledgeexchange.techtarget.com/itanswers/… – Pokelogan

@Pokelogan I work in a data warehouse. If I could keep the user view static until a very quick replacement of the content (i.e. I can rebuild the warehouse without disturbing the users, then flip a switch and they see new data) I would. – Shutout

Answer 12

When complex queries are encounter with Oracle it will take more time to execute that query .if user want to reduce time of execution then materialized view is best for that .firstly we have to create materialized view with that query after creating we can simply use materialized view instead of base table .

Teerell