Assignment: Usage & Advantages of 'view' and 'materialized view' with examples

 

# Usage of 'view' and 'materialized view' with examples

 

Definition

Technically View is a logical virtual copy of the table created by “select query” but the result is not stored anywhere in the disk. Every time we need to fire the query when we need data, so always we get the latest data from original tables from where the Views are created.

On other hand Materialized views are also a logical virtual copy of data-driven by the “select query” but the result of the query will get stored in the table or disk.

 

Examples

The first advantage of View over the materialized view is that in Views; query result is not stored in the disk or database but Materialized view allow to store the query result in disk or table. So Views are minimizing the cost where materialized views are costly.

One more advantage of View is that in the case of View we always get the latest data but in the case of the Materialized view we need to refresh the view for getting the latest data.

CREATE VIEW regional_view AS SELECT region, population FROM region_table WHERE region=’DHAKA’;

DROP TABLE regional_mv CREATE TABLE regional_mv (region varchr(10) not null,  population number(10) not null); INSERT INTO regional_mv SELECT region, population FROM region_table WHERE region=’DHAKA’;

 

 

The big advantage of a Materialized View is extremely fast retrieval of aggregate data, since it is precomputed and stored, at the expense of insert/update/delete. Where the view cost more resources for getting the same results from the table.

The database will keep Materialized View in sync with the real data with REFRESH procedure.

Materialized View can really help to build/manage a Data Warehouse or an Archive to have better performance for querying the results for data those will be not update further like student’s result archive, alumni information bank, evolution etc.