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