What is the difference between View and Materialized View?

Asked

Viewed 7,839 times

15

According to Oracle documentation, a Materialized View is a database object that contains the results of a query and a View is a logical table based on one or more tables or views and does not contain data itself.

Okay, the documentation talks about what each one is and has even more details. But for laypeople, what is really the difference between the two? There is a difference between performance and procedural costs of the database?

database oracle terminology sql-view

2 answers

15


A good part of this question is already answered more fully in What are SQL views? What advantages and disadvantages to using? (has example of use there and advantages and disadvantages of use of view)

View

To view is a simple query (enough to be an SQL code) stored in the database that creates an illusion of being a table, and can be used in several operations to:

  • simplify the darlings and facilitate access to certain information

  • conform better to the logical model

  • allow better control of data access for certain users

    One can create a view with certain columns and give access permission to a user or group for that view, not for the physical table, then it will only have access to these data.

The entire dataset of view is generated at the time it is requested (if no optimization is done by the database). And that’s the only cost, which can’t even be considered exactly extra because if you’re using it, you’d probably need to do it anyway.

Materialized view

This is a view which creates an auxiliary table for storing data from query established by view. Thus the database creates a kind of automatic trigger so that every data update in the columns involved also updates the materialized view (auxiliary table), thus allowing direct access to the data without further processing in a query.

  • With it you gain data access performance, but you get a higher data update cost. Need to analyze what is most interesting in each case. So this is an access optimization.
  • It obviously takes up space on "disk".

These are her main advantages and disadvantages over view normal.

Apart from the fact that the data is stored, they work in essentially identical ways (they may vary somewhat depending on the database provider).

It is possible to simulate a materialized view in any database that has a Trigger.

  • 1

    In postgres you update a materialized view via the REFRESH command, there is no automatic trigger to update the materialized view, so it is not all banks that implement this automatic trigger. https://www.postgresql.org/docs/current/static/sql-creatematerializedview.html

  • 3

    @Murillohenrique is true, is that the context of the question is more on top of the same Oracle.

10

Basically, the View materialized is a separate data object, while the View is a projection on top of other data objects, calculated from the moment some selection operation is done on it.

In terms of performance, the View materialized is usually superior to a View traditional because there are no costs of joins, path of indexes, parameterizations and other factors, because the result of the execution was exactly materialized in a data object with own columns, data and statistics.

Browser other questions tagged database oracle terminology sql-view

You are not signed in. Login or sign up in order to post.