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
TAGS : SQL SQL TUTORIAL SQL STUDY ONLINE
No comments:
Post a Comment