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