MVs vs. Views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: MVs vs. Views

  1. #1
    Join Date
    Oct 2002
    Posts
    182

    MVs vs. Views

    We have a check on many queries in our application that joins 3 tables together. I want to create a view or MV so as to improve performance.

    After reading up on MVs and Views I am a little confused what would be best.

    The end result does not do aggregates and only checks against 3 or 4 fields that lie in the 3 tables of the join.
    The data does not change or update very often in any of the 3 tables.
    Also, the 3 tables do not hold a large amount of data.

    Are MVs MAINLY for aggregates or very large tables? Or are they more efficient than views in general?

    The only requirement would be that when new rows periodically get inserted into the 3 tables (they are dependant upon each other) that the view or MV reflect the new rows.

    Thanks for your advice.
    - Cookies

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    A view will doing nothing for your performance -- all it does is "encapsulate" a query. It still gets executed the same as the original query would.

    A materialized view could provide a pre-joined, indexed result set based on all, or a limited set of, the base table columns, but if you want it to update automatically every time a base table changes ("on commit" option) then you introduce an overhead for the query that is performaing the insert, update or delete on the base tables. You will want to minimize the impact by performing fast refreshes which means placing snapshot logs on each base table.

    If you go this route, note that Oracle recommend that if you are going to insert some rows, delete some rows, and update some rows as part of the same transaction then you can improve refresh performance by commiting between each type of operation -- insert/commit/delete/commit/update/commit.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    Thanks for your comments.
    - Cookies

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