Solved

Views vs MVs

Posted on 2014-01-20
4
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

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 74

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

636 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