Solved

Views vs MVs

Posted on 2014-01-20
4
239 Views
Last Modified: 2014-01-21
Why do we prefer views or materialized views..  I know views has advantages like restricting access to all columns for security but we can select few columns in MVs as well.  Could you please suggest the advantages of MVs here.
0
Comment
Question by:d27m11y
  • 2
4 Comments
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
Comment Utility
If a query takes a really long time to run the MV can have the results pre-computed for you.

This assumes the data changes slowly enough that generating the results into the MV will still be valid when a user queries them
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 125 total points
Comment Utility
A view is a "window" into certain data in your database.  When you select data from a view, the database engine still has to fetch the data, perform the relevant I/O's and return the data to you.

If you have a complex query that may run for say 5 minutes, you have to sit and wait for that to happen.

A view does not occupy any space in the database, it's just a definition of an defined query into the data.

A Materialized View is a physical database object.  It is refreshed from the primary data sources on a preset value (maybe hourly, maybe nightly) and could be used to populate data in a data warehouse, for example.

If you have complex computations or access paths that need to be done, that work is done while the MV is being refreshed so when you access data from the MV, all the calculations and whatever else was being done that takes 5 minutes with a regular view should all have been done while the MV was being refreshed, so theoretically you get your data NOW instead of waiting.

Of course, views get you real time data.  MV's get you data as of the last refresh (when ever that was).

If you've got any more specific question, feel free to ask, hope that gets you pointed in the right direction.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
Comment Utility
For example,   you could have a query like this...


select * from table1@db_link1 t1 jeft join table2@db_link t2
on t2.id = t1.id

pulling data across 2 database links in order to generate a local result can be an expensive and time consuming operation, if your network connection is unreliable the query may simply fail.

Using a materialized view of that query, the results could be generated once, saved and then not generated again until data changed on one or both of the remote systems.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 125 total points
Comment Utility
Views and MVs have a set of distinct features/purpose for which they are existing. Please refer to the below links which give comprehensive and simple to understand the point by point differences between these 2 objects.

https://community.oracle.com/thread/663466?start=0&tstart=0
http://its-all-about-oracle.blogspot.sg/2013/06/difference-between-view-and.html
http://www.oratable.com/views-materialized-views-difference/

Thanks,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now