Home » SQL & PL/SQL » SQL & PL/SQL » Need for Materialized Views
Need for Materialized Views [message #437094] Thu, 31 December 2009 01:20 Go to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
Hi All,

I was going through the concepts of pl/sql when i came across Materialized views, i understood the definition

"A materialized view or snapshot contains the results of a query of one or more tables each of which may be located on the same or on a remote database. Materialized viwes allows to create replications of tables of remote data with a read-only privillage on the copy. When a materialized view is created Oracle creates one table and index and in the same schema of the materialized view."

But i didnt understand the scenario when materialized view would be required.

Please help me in understanding this.

Thanks,
Asha
Re: Need for Materialized Views [message #437098 is a reply to message #437094] Thu, 31 December 2009 01:35 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Materialized Views (MV's) are a good way of summarizing a large number of detail rows into smaller objects. They are very useful because they pre-compute long operations. In fact, they are query results that are stored in a different object. We can use materialized views in data warehouses to increase the speed of queries on very large databases.

Scenario: You need to make a report from a table which having a volatile data.
eg, you need to display the counts of the queues sitting at different locations based on the time that the queues comes into your system. But the thing is that it may move to different locations based on certain logic.So data is volatile.

Now you need to generate a report for the counts for those queues sitting in your application which are less than 30 min old, < 60 min old etc etc.

Also the requirement is that the moment you want to have details for the queue it should give the count,location and other necessary details. You can create a materalised view with fast refresh for 5 min interval to get the precomputed data.

like this:
queue  0-30 min  30-60 >60 min
p1      8         7     10
p2      7         4     100
p3      28        45    112


Hope this helps.



Regards,
Ved


[Updated on: Thu, 31 December 2009 02:41] by Moderator

Report message to a moderator

Re: Need for Materialized Views [message #437104 is a reply to message #437094] Thu, 31 December 2009 01:57 Go to previous messageGo to next message
ashamg2k
Messages: 18
Registered: October 2007
Junior Member
Thanks a lot Ved, it was really helpful.

Would like to know if MVs are mostly used in Datawarehousing?

Thanks in advance,
Asha
Re: Need for Materialized Views [message #437105 is a reply to message #437104] Thu, 31 December 2009 02:01 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

Would like to know if MVs are mostly used in Datawarehousing?

Read my previous post answer is there.


Materialized view


Regards,
Ved

[Updated on: Thu, 31 December 2009 02:02]

Report message to a moderator

Previous Topic: Log details for exception for the sql
Next Topic: Issues Oracle Data Source
Goto Forum:
  


Current Time: Wed Dec 07 08:41:30 CST 2016

Total time taken to generate the page: 0.16074 seconds