Question

Views are virtual in database but Materialized view are persistent. Discuss the need to have materialized view instead of views and in what condition No materialization if preferred.

Answer 1

Views :

• Views are similar to the tables in database, but the views are virtual, they are not stored anywhere in the database (but the query expression is stored).
• Consist of rows and columns.

Materialized view :

• Also similar to tables in database, but they are not virtual.
• Here the data is stored in the database.

The need to have materialized view instead of views :

• When we have to use a view frequently, we use materialized view instead of view.
• Using materialized view reduces the computation time, because in materialized view datas are stored in database.
• Materialized view is also used in the case where a view is defined is large, and result is very small.
• If we have a case in which, when we update the table from which a view is derived the view also need to be updated, we use a materialized view.
• For faster access of data materialized view is preferred.

In what condition no materialization is preferred :

• No materialization is preferred in the situations where we need to minimize the usage of disk spaces. Materialization will duplicate the data already stored in the database which will increase the space usage.