Maintenance Operations Required for Materialized Views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Maintenance Operations Required for Materialized Views

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Maintenance Operations Required for Materialized Views

    We see some performance benefit in using Materialized Views in our OLTP environment. We are using MV logs and ROWID MVs - in the absence of explicitly defined primary keys on the base tables - understand that it is advisable not to use ROWID MVs.

    Looking out for information about the maintenance activities that need to be carried out for MVs - yet to find the same in the docs. In the meantime, I thought I could raise it up here so that those who are using MVs in their environment can share their experience on the same.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    In my experience Materialized Views are great for Data Warehousing because of query rewrite feature, they are also pretty good to solve some simple replication requirements yet I'm still waiting to see how they could help in a properly designed OLTP environment.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    It is providing performance benefit during "batch processing", where about 5 years history data is being loaded from multiple tables.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by ggnanaraj View Post
    It is providing performance benefit during "batch processing", where about 5 years history data is being loaded from multiple tables.
    ETL? That's a very unusual operation to do in a system defined as being OLTP.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Oct 2006
    Posts
    175
    We too have defined MVs in our OLTP, with refersh on DMLs; we hardly have few hundreds of transactions a day. This is coming out to be a major bottleneck for our application performance.

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