Views and Materialized Views in Oracle
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.
(also called Snapshots in older versions) are database object that contains the
There exists common difference between Views and
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.
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