Difference between View and Materialized
view is one of the popular SQL interview question, much like truncate vs delete, correlated vs
noncorrelated subquery or primary key vs
unique key. This is one of the classic question which
keeps appearing in SQL interview now and then and you simply can’t afford not
to learn about them. Doesn’t matter if you are a programmer, developer or DBA,
this SQL questions is common to all. Views are concept which not every
programmer familiar of, it simply not in the category of CRUD operation
or database
transactions or SELECT query, its little advanced concept for
average programmer. Views allows a level of separation than original table in
terms of access rights but it always fetch updated data. Let’s see What is View
in database, What is materialized View and difference between view and
materialized view in database.
What
is View in database
Views are logical virtual
table created by “select query” but
the result is not stored anywhere in the disk and every time we need
to fire the query when we need data, so always we get updated or latest data
from original tables. Performance of the view depend upon our select query. If
we want to improve the performance of view we should avoid to use join statement
in our query or if we need multiple joins between table always try to use index
based column for joining as we know index based columns are faster than non
index based column. View allow to store definition of the query in the database
itself.
What
is Materialized View in database
Materialized views are also
logical view of our data driven by select query but the result
of the query will get stored in the table or disk, also definition of the
query will also store in the database .When we see the performance of
Materialized view it is better than normal View because the data of
materialized view will stored in table and table may be indexed so
faster for joining also joining is done at the time of materialized views
refresh time so no need to every time fire join statement as in case of view.
Difference
between View vs Materialized View in database
Based upon on our
understanding of View and Materialized View, Let’s see, some short difference
between them :
1) First difference
between View and materialized view is that, In Views query result is
not stored in the disk or database but Materialized view allow to
store query result in disk or table.
2) Another difference
between View vs materialized view is that, when we create
view using any table, rowid of view is same as original table
but in case of Materialized view rowid is different.
3) One more difference
between View and materialized view in database is that, In case of
View we always get latest data but in case of Materialized view we need to
refresh the view for getting latest data.
4) Performance of View is
less than Materialized view.
5) This is continuation of
first difference between View and Materialized View, In case of view its only
the logical view of table no separate copy of table but in case of Materialized
view we get physically separate copy of table
6) Last difference between
View vs Materialized View is that, In case of Materialized view we need extra
trigger or some automatic method so that we can keep MV refreshed, this is not
required for views in database.
When
to Use View vs Materialized View in SQL
Mostly in application we
use views because they are more feasible, only logical
representation of table data no extra space needed. We easily get replica of
data and we can perform our operation on that data without affecting actual
table data but when we see performance which is crucial for large application
they use materialized view where Query Response time matters so Materialized
views are used mostly with data ware housing or business intelligence
application.
EmoticonEmoticon