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.

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.

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.

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.

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.

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.

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.

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 it by two parallel process if one has not ended and the other needs the info up to that instant in time. 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.

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).

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.

Answer 10

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.

Answer 11

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.

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 .