What is difference between View and Materialized View
in Database or SQL?
Difference between View
and Materialized view is one of the popular SQL interview question, much like truncate
vs. delete, correlated vs. no correlated sub query 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 View is in database, what 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.
No comments:
Post a Comment