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.
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
– Murillo Henrique
@Murillohenrique is true, is that the context of the question is more on top of the same Oracle.
– Maniero