# 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 FROM region_table |
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 |
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.