It might be great confusion to the novice
regarding views and Materialized views in SQL. Indeed, they are defined by SQL
statement. Let us discuss the key differences between Views and Materialized
views and hope at the end this blog you might be clear with it.
Firstly, what is a View?
A view in SQL is nothing but a virtual table; it just queries and shows the data
from the base Table.
Then, you might be thinking what its Purpose is?
A view provides additional level of table Security by restricting access to set
of rows/columns in a Table.
Materialized Views
Materialized views (also called Snapshots in older versions) are database
object that contains the Pre-computed results.
There exists common difference between Views and Materialized Views.
- When a view is executed, the results are
retrieved from the view's base table and they are displayed, whereas the
query is executed when the materialized view is refreshed. As they contain
pre-computed results we only browse the result.
- A view occupies no space except the
definition of it in the Data Dictionary, whereas a Materialized view
occupies space. It exists in the database in the same way as a table in the
database.
- A view shows real-time data from the base
table, whereas Materialized view is up-to-date when it was refreshed last
time.
Usage
A view is best used when
- Restricting rows/columns in base tables.
- To hide the implementation complexity as
the view may be basically formed with a single SQL or joins with multiple
tables.
Materialized view is best
- When you are performing queries on a big
table and you want faster response.
- If you don't mind the results are being
little out-of-date where we may have frequent access to the table.
What's you Plan, whether to create a View or
Materialized View??
Thanks for Reading