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.
That’s all on difference between
View and materialized View in database or SQL. I suggest always prepare
this question in good detail and if you can get some hands on practice like
creating Views, getting data from Views then try that as well.
Other SQL Interview Question articles for you
No comments:
Post a Comment