Oracle Material Views and Analytic Workspaces
In Oracle, what are the pros and cons of using materialized views and analytic workspaces? What are the best practices related to using these features?
We have an OLTP system, but would also like to have access to summary information in reports and interactive decision support tools.
source to share
I've never used analytic workspaces, but there are some real downsides to MV (although we do use them). A couple of things:
-
they are essentially tables. This means they take up space. In some of our situations, we use them to access remote data in tables with millions of rows. Views are query results and virtual. MVs are up to date (there are real strings, not memory structures).
-
One of the other problems we ran into is that often, when the MV doesn't work when updating, it will never try to update again.
For us, we believe that this happens in two situations:- in the case of a remote system, if the connection to that remote system is dropped during the upgrade, the MV will never be able to upgrade again and may have to be dropped and rebuilt.
- The same happens if one of the MV tables is based on a change structure. So, for example, I have an MV that is built using a "select * from mywork" query. At some point I issue an alter command to change the FirstName column from varchar2 (100) to varchar2 (150). At this point, we often see MV updates fail and never recover.
The specifics are not really important, the important thing is that you will definitely need to monitor your MVs to make sure their data is up to date.
source to share
I haven't used analytic workspaces, so I can't talk to them.
However, materialized views can be very useful. They basically cache the results of the view so you can do things like build indices. Depending on how they are configured (with or without logs), they can become obsolete compared to live data, but significantly faster (depending on your data and queries).
If you are dealing with remote data (database links), materialized views allow you to cache data locally. If you are doing slow computations on data, materialized views may allow you to cache the results (which may be outdated). In the meantime, there is no need to know about it. ”
Materialized views can be very handy, they just have to be implemented wisely.
source to share