Solved

Views vs MVs

Posted on 2014-01-20
4
243 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
ID: 39795497
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
ID: 39795498
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
ID: 39795505
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
ID: 39796142
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

777 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