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.

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.

  1. 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.
     
  2. 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.
     
  3. 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

  1. Restricting rows/columns in base tables.
     
  2. 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
 

  1. When you are performing queries on a big table and you want faster response.
     
  2. 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