Search code examples
sqloracle-databaseplsqlviewolap

Oracle material views vs analytic workspaces

In Oracle, what are the pros and cons of using materialized views, and of analytic workspaces? What are the best practices surrounding the use of these features?

We have an OLTP system, but would also like to access summary information in reports and interactive decision support tools.

Solution

  • I have never used analytic workspaces either, but there are some real cons to MVs (though we do use them). Couple things:

    1. they are, in effect, tables. That means they take up space. In some of our situations, we are using them to help access remote data on tables with millions of rows. Views are query results, and virtual. MVs are actual (there are real rows, not memory structures).

    2. One of the other problems we've run into is that, often, when an MV fails on refresh, it will never try and refresh again.
      For us, we believe this is happening in two situations:

      • in the case of a remote system, if the connection to that remote system is dropped during a refresh, the MV may never refresh again and may have to be dropped and rebuilt.
      • the same thing seems to happen if one of the tables the MV is based on changes structure. So, for example, I have an MV that is built using the query "select * from mywork". At some point, I issue an alter command to change the column FirstName from varchar2(100) to varchar2(150). At that point, we often see the MV refreshes fail and never recover.

    The specifics really aren't important, what is important is that you will definitely have to monitor your MVs to make sure their data is up to date.