This discussion is archived
10 Replies Latest reply: Nov 24, 2012 7:47 AM by ranit B RSS

use of materialized views?

ratnakar Newbie
Currently Being Moderated
can any one tell me the use of materilized views in oracle?
explain briefly please...........
  • 1. Re: use of materialized views?
    ranit B Expert
    Currently Being Moderated
    ratnakar wrote:
    can any one tell me the use of materilized views in oracle?
    explain briefly please...........
    In Views, what happens is there is not data storage in it. Every time you execute the View, it has to pull the data using its query from the Base tables.

    Materialized Views are replica (or Snapshot ) of data from some master site to another site.
    It has the capability to store data. When you execute the MView it pulls data stored in it.

    There is something called as MView Refresh which pulls data from the base tables and stores in this MView's storage unit. And when you read the MView data is pulled from this storage unit (and doesn't hit the base tables).

    This somewhat ensures data availability always.

    Please read this thoroughly... http://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm
  • 2. Re: use of materialized views?
    ratnakar Newbie
    Currently Being Moderated
    THANKS FOR UR RESPONSE...
    AND MV CONTAINS PHYSICAL DATA..BT WHAT IS THE USE OF THAT REPLICA.
  • 3. Re: use of materialized views?
    ranit B Expert
    Currently Being Moderated
    AND MV CONTAINS PHYSICAL DATA..BT WHAT IS THE USE OF THAT REPLICA.
    I didn't get your question exactly... Could you please ask clearly?
  • 4. Re: use of materialized views?
    ratnakar Newbie
    Currently Being Moderated
    yes,..mv is replica bt what is the use...when we can use in real time scenarios...
  • 5. Re: use of materialized views?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    ratnakar wrote:
    THANKS FOR UR RESPONSE...
    AND MV CONTAINS PHYSICAL DATA..BT WHAT IS THE USE OF THAT REPLICA.
    Usually, a materialized view is not just a copy of a table. Usually, some kind of transformation takes place. For example, a materialized view may countain the results of a GROUP BY on a table, or analytic functions applied to the table, or a join or tables, or a UNION of tables, or some combination of these kinds of things. Querying the materialized view can be much faster than querying a regular view (or the base tables) because much of the work is done when the materialized view is refreshed, before the query is performed.

    You can learn about materialized views from the Database Concepts manual<a> and other documentation.
  • 6. Re: use of materialized views?
    ranit B Expert
    Currently Being Moderated
    ratnakar wrote:
    yes,..mv is replica bt what is the use...when we can use in real time scenarios...
    Uses -
    1] Security : Outside access to base table data is restricted.
    2] Availability : Data is always available and no need of executing queries every time and hitting Base Tables.
    3] Faster data fetch.
    4] Stores data after fetching and applying some business

    Check this (nicely explained) --- http://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm#i44564

    You can Google for more.. :-)

    *@Frank* - Please rectify me if I'm wrong. I believe your words a lot.


    Edited by: ranit B on Nov 24, 2012 9:02 PM
    -- added link
    -- added Note to Frank
  • 7. Re: use of materialized views?
    ratnakar Newbie
    Currently Being Moderated
    thank you ranit.....
  • 8. Re: use of materialized views?
    ranit B Expert
    Currently Being Moderated
    Are you clear now? Did those help you...?
    If not, you can still ask your doubts until you get clear.

    Ranit B.
  • 9. Re: use of materialized views?
    ratnakar Newbie
    Currently Being Moderated
    its very useful............thank u very much
  • 10. Re: use of materialized views?
    ranit B Expert
    Currently Being Moderated
    ratnakar wrote:
    its very useful............thank u very much
    But you marked as 'Correct' and not 'Useful'... hehe... Thanks ;-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points