Materialized look at tables: what are the benefits?

Question

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

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

Value, you can create 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; 

This is not to say that ease of creation / maintenance is not enough for an advantage, I just want to make sure that I haven't missed anything.

Answer 1

Dynamic rewrite of requests . Materialized representations determine not only relationships, but also allow you to combine costly associations and aggregations in advance. The optimizer is smart enough to use MV to get the corresponding data, even if MV is not used explicitly in the query (taking into account the database settings, etc.).

Your question has been tagged as Oracle, but MSSQL also performs similar tricks.

Answer 2

They are basically equivalent, but MV has various options for automatically updating data, which not only improves ease of maintenance, but also, in some cases, efficiency, since it can track changes line by line.

Answer 3

Materialized views can be updated — these are snapshots of data taken at regular intervals.

Your second statement is just a one-time deal - the data is inserted into the table at that moment. Further changes to the source data are not reflected in the table.

Answer 4

The big advantage of Materialized View is the extremely fast extraction of aggregated data, as it is pre-computed and stored by insert / update / delete. The database will keep the Materialized View in sync with real data, no need to reinvent the wheel, let the database do it for you.

Answer 5

• The materialized view will remain synchronized with the underlying relationships it depends on.

• If the materialized view is updated, then when the materialized view changes, it will also change the underlying relationship on which it depends.

Answer 6

In addition to the other answers (because I did not see them), I would say that although both of them use space, the materialized view is logically normalized, and the additional table is logically denormalized. If this is something that is not temporary one-time, you will have to remember to update the second table when updating the base table.

Answer 7

1) Speeding up write operations . Since indexes can be created on materialized views, reading from them is very fast. Please note: if you create an index in a table that includes a large number of records, the service index overhead slows down the writing process. To avoid this, you can create a materialized view and create indexes on it. These indexes can be saved in the background and not adversely affect table write operations.

2) Speeding up read operations : complex connections; which can take ages, can accelerate by creating indexes on materialized representations. This is very convenient in most reporting scenarios.

Answer 8

The difference between the table and MV is the table, you can perform DML operations that will be visible to other users, while the changes you make to MV will not be available to other users until you upgrade the database server.

MV has another advantage, when you create MVs based on multiple tables using complex queries, users increase dramatically when using MVs.

Answer 9

In addition to the benefits already mentioned:

• rewriting dynamic queries (in short, the DB optimizer knows how MV is created, so it can reuse it to optimize other queries)
• optionally, automatically, incremental update is possible,

I would like to mention:

• some materialized views can be written that update the original table (for example, joins with primary keys can be written, on the contrary, if the materialized view is the result of a group with which it cannot be written)
• the database server saves the query that created the data and can restart it. If you are creating a table, you need an external tool (perhaps only a custom script) to re-run the query when the user needs an update. (I work for a company developing a tool that does this and more).