Statistics and Bitmap Indexes

The scripts for creating dimension materialized views, whether generated by OLAP Summary Advisor or DBMS_ODM, include syntax for gathering statistics and creating bitmap indexes.

Statistics

Statistics are required by the optimizer in order to maximize query performance at runtime.

The following SQL statements analyze a materialized view and generate the needed information.

ANALYZE TABLE mv_name COMPUTE STATISTICS;
EXECUTE dbms_stats.gather_table_stats (mv_owner, mv_name, degree=>
   dbms_stats.default_degree,method_opt=>'for all columns size skewonly') ;
ALTER TABLE mv_name MINIMIZE RECORDS_PER_BLOCK ; 

For more information about the ANALYZE TABLE statement, refer to the Oracle9i SQL Reference. For more information about the DBMS_STATS package, refer to the Oracle9i Supplied PL/SQL Packages and Types Reference.

Bitmap Indexes

Bitmap indexes optimize the performance of materialized views at runtime. Dimension materialized views for the OLAP API include bitmap indexes for all columns that contain dimension values.

The following SQL statements create bitmap indexes.

CREATE BITMAP INDEX index_name ON mv_name (mv_colname )
TABLESPACE tblspace_name 
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

The CREATE Statement for a Dimension Materialized View

The following example shows the basic structure of the SQL statements generated by OLAP Summary Advisor or DBMS_ODM to create a dimension materialized view for the OLAP API.

The SELECT statement contains a COUNT(*) function, a GROUPING_ID function, MAX aggregate functions, and a ROLLUP function. The following example shows the basic syntax.

CREATE MATERIALIZED VIEW mv_name
PARTITION BY RANGE (gid) 
   (partition values less than(1) , 
    partition values less than(3) ,
     .
     .
     partition values less than(MAXVALUE))
TABLESPACE tblspace_name
BUILD IMMEDIATE
USING NO INDEX
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT 
   COUNT(*) COUNT_STAR, 
   GROUPING_ID(level_cols) gid,
   MAX(attribute_col1)
   .
   .
   MAX(attribute_coln)
level_cols 
FROM
   dimension_table
GROUP BY level1, ROLLUP(level2, ..., leveln)

where:

mv_name is the name of the materialized view. The name is derived from the names of the dimension table and the hierarchy.

level_cols are the names of columns in the dimension table that contain data for the levels of the hierarchy, beginning with the most aggregate (level1) and ending with the least aggregate (leveln).

attribute_col is the name of a column defined as an attribute. All columns defined as attributes should be listed in a MAX function.

dimension_table is the name of the dimension table whose columns are being aggregated to create the materialized view.

level1 is the highest level of aggregation. Note that level1 is excluded from the ROLLUP list.

leveln is the lowest level of aggregation or "leaf node", which is also the key column.