Optimizing the Database for OLAP

A basic feature of online analytical processing (OLAP) is the ability to analyze and view various levels of aggregate data. Queries generated by the Oracle OLAP API use the database's query rewrite capability whenever possible. Query rewrite enables a query to fetch aggregate data from materialized views rather than recomputing the aggregates at runtime.

The creation of materialized views and indexes can significantly improve the performance of analytical queries generated by the OLAP API.

About Materialized Views

Materialized views store data that has been calculated from detail tables. When data in the detail tables changes, you can refresh materialized views with the new data. While a view only stores the query, a materialized view actually stores the results of a query. Thus, you will need to allocate sufficient tablespace to store the required materialized views.

For query rewrite to recognize that a materialized view contains the query results, the materialized view must have been created using basically the same type of SQL commands that Oracle OLAP generates. The OLAP API requires a very specific set of materialized views.