What is the difference between View and Materialized View?

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 laymen, what is really the difference between the two? Is there a difference between performance and procedural costs of the database?

    
asked by anonymous 30.06.2016 / 17:23

2 answers

15

Much of this question is already answered more fully in What are SQL views? What are the advantages and disadvantages to using? (there are examples of use there and advantages and disadvantages of using view )

View

The view is a simple query stored in the database that creates an illusion of being a table, and can be used in several operations to:

  • simplify queries and facilitate access to certain information
  • better conform to the logical model
  • Allow better control of data access for certain users

    You can create a view with certain columns and grant access to a user or group for this view , not the physical table, access to this data.

Every view dataset is generated the moment it is requested (if no optimization is done by the database). And this is the only cost, which can not even be considered extra-exact because you're using it, you probably would need to do it anyway.

Materialized view

This is a view that creates an auxiliary table to store the query data set by view . So the database creates a kind of automatic trigger so that any data update in the columns involved also update the materialized view (auxiliary table), thus allowing direct access to data without further processing in a query.

  • With it, you gain data access performance, but it has a higher cost of updating the data. You need to analyze what is most interesting in each case. So this is an access optimization.
  • It obviously takes up disk space.

These are the main advantages and disadvantages of it over the normal view .

Apart from the fact that the data is stored, they work essentially identically (it may vary slightly depending on the database vendor).

You can simulate a materialized view in every database that has a trigger mechanism.

    
30.06.2016 / 18:29
9

Basically, materialized View is a separate data object, whereas traditional View is a projection on top of other data objects, calculated from any selection operation is made on it.

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

    
30.06.2016 / 17:27