aterialized views are disk based and update periodically base upon
the query definition.
Views are virtual only and run the query definition each time they
are accessed.
Views evaluate the data in the tables underlying the view definition at the
time the view is queried. It is a logical view of your tables, with no data
stored anywhere else. The upside of a view is that it will always return the
latest data to you. The downside of a view is that its performance depends on
how good a select statement the view is based on. If the select statement used
by the view joins many tables, or uses joins based on non-indexed columns, the
view could perform poorly.
Materialized views are similar to regular views, in that they are a logical
view of your data (based on a select statement), however, the underlying query
resultset has been saved to a table. The upside of this is that when you query a
materialized view, you are querying a table, which may also be indexed. In
addition, because all the joins have been resolved at materialized view refresh
time, you pay the price of the join once (or as often as you refresh your
materialized view), rather than each time you select from the materialized view.
In addition, with query rewrite enabled, Oracle can optimize a query that
selects from the source of your materialized view in such a way that it instead
reads from your materialized view. In situations where you create materialized
views as forms of aggregate tables, or as copies of frequently executed queries,
this can greatly speed up the response time of your end user application. The
downside though is that the data you get back from the materialized view is only
as up to date as the last time the materialized view has been refreshed.
Materialized views can be set to refresh manually, on a set schedule, or
based on the database detecting a change in data from one of the underlying
tables. Materialized views can be incrementally updated by combining them with
materialized view logs, which act as change data capture sources on the
underlying tables.
Materialized views are most often used in data warehousing / business
intelligence applications where querying large fact tables with thousands of
millions of rows would result in query response times that resulted in an
unusable application.
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.
What is the difference between Views and Materialized Views in
Oracle?,古老的榕树,5-wow.com
