Comparing Materialized Views and
Analytic Workspaces in Oracle
Database 11g
An Oracle White Paper
March 2008
Comparing Materialized Views and Analytic Workspaces in Oracle Database 11g Page 2
Comparing Materialized Views and Analytic
Workspaces in Oracle Database 11g
Introduction ....................................................................................................... 3
Fast Reporting – Comparing The Approaches............................................. 3
Overview of Materialized Views................................................................. 3
Overview of Analytic Workspaces ............................................................. 4
Working Together in the Oracle Database ............................................... 6
Explanation of Data Used for this White Paper........................................... 6
Methodology for Designing and Building the Materialized Views........ 7
Manually Creating the Materialized View ............................................. 7
Generating Recommendations from the SQL Access Advisor ........ 9
Methodology for Defining the Analytic Workspace ............................. 10
Tools For Querying ........................................................................................ 15
Refreshing the Data ........................................................................................ 16
Refreshing Materialized Views.................................................................. 16
Refreshing Analytic Workspace Cubes.................................................... 17
Other Issues To Consider .............................................................................. 17
Attribute Presence For Querying......................................................... 18
Data Completeness and Sparsity.......................................................... 18
Supporting Join Performance............................................................... 19
Aggregate Rollup ............................