Materialized views the good, bad and the ugly.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Materialized views the good, bad and the ugly.

  1. #1
    Join Date
    May 2005
    Posts
    129

    Materialized views the good, bad and the ugly.

    General question on mviews. I started my new job 2.5 miles from my home yesterday. I am in the hot seat to recommend the most suitable features of Oracle 10g to various development groups. Its a neat-o job position but I am going to be slammed for the forseeable future.

    I am going to clarify the teams understanding of Mviews today but would like to hear some real world application Do's and Dont's if you have them. In past data warehouses we did this all manually so I have only read about Mviews academically. For instance it looks as if REFRESH ON COMMIT might be bad for an OLTP system and would be better off done all at once off hours - business allowing.

    Thanks for any and all commentary.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Mviews are great, except when they're not.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Quote Originally Posted by marist89
    Mviews are great, except when they're not.
    Answers like this are why you are a supper genious!!!

    If I had to take a swag at it I would say that they are best for data that does not change often. I haven't worked with them lately, but when I was researching them, I found that normal views worked better for the data and queries that I was working with at the time, and yes I did have query rewrite turned on.
    this space intentionally left blank

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by gandolf989
    Answers like this are why you are a supper genious!!!
    Without more information, there's really no right answer to roadwarriorDBA's question.

    If I had to take a swag at it I would say that they are best for data that does not change often. I haven't worked with them lately, but when I was researching them, I found that normal views worked better for the data and queries that I was working with at the time, and yes I did have query rewrite turned on.
    depends. If you can use fast refresh, then the rate at which the data changed is less relavent.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2005
    Posts
    129
    This my take on it, some from Jlewis and some from Akadia.

    Pre-Joined real table result ? Yes.

    "A materialized view creates a real table, and this table may
    be indexed, analyzed, and so on."

    "Materialized views will increase your need for one resource -
    more permanently allocated disk. We need extra storage space
    to accommodate the materialized views, of course, but for the
    price of a little extra disk space, we can reap a lot of benefit."

    The Mviews roots are in snapshots and what has been added is
    insight into how to employ Query Rewrite as well as
    automatically funneling changes from the base table to the
    Mviews or summary table which are real tables occupying space.

    Out of control Optimizer ? Not necessarily.

    "Sometimes, you know Oracle could use the materialized view,
    but it is not able to do so simply because Oracle lacks
    important information."

    I think we may want to look at QUERY REWRITE INTEGRITY set to
    either
    - TRUSTED
    - STALE TOLERATED

    I think we will not want to use REFRESH ON COMMIT as it will
    add overhead to modifications performed on the base tables in
    order to capture the changes. Mviews with refresh on commit
    have a significant overhead at commimt. They also introduce
    odd performance side effects if the query rewrite integrity is
    enforced because a query against uncommitted data has to visit
    the base tables whereas a query against committed visits the
    mview. I will look at 'Mview Logs' and see if its just
    recording the delta or aggregating the entire MVIEW again.

    We could instead force all base table changes to be funneled
    to the Mview each nite, liberating the OLTP changes from the
    Mview synchronizations.

    This is a good read on Mviews -

    http://www.akadia.com/services/ora_m...zed_views.html - I
    suspect its not current so I will hit the Oracle 10g docs and
    see what the features and variations are today.

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    A nice and usefull feature for refreshing is Partition Change Tracking (PCT). You may have look at it either on the Oralce Doc or on metalink.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Mviews may be bad, may be good. It all depends upon how you use them.
    The only way you can figure it is:
    Benchmark, Benchmark, Benchmark.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width