Oracle material views vs analytic workspaces

Question

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.

Answer 1

I haven't used analytic workspaces, so cannot speak to them.

However, materialized views can be very useful. They are essentially cached view results that you can do things such as build indexes on. Depending on how they're configured (with or without logs), they can become stale compared to "live" data, but are significantly faster (depending on your data and queries).

If you're dealing with remote data (database links), materialized views let you cache the data locally. If you're doing slow calculations on data, materialized views can let you cache the results (which may be stale).

Materialized views can be very handy, they just need to be implemented wisely.

Answer 2

I have never used analytic workspaces either, but there are some real cons to MVs (though we do use them). 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).

1. 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:
2. 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.
3. 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.