Partitioning, Statistics, and Indexes

The scripts generated by DBMS_ODM.CREATEFACTMV_GS include syntax for partitioning, gathering statistics, and creating bitmap indexes.

Partitioning

Partitioning can have a significant impact upon query performance. You may want to customize the partitioning of fact materialized views before running the scripts generated by DBMS_ODM.CREATEFACTMV_GS.

By default, partitioning is based on grouping IDs since most queries are based on levels. A grouping ID uniquely identifies one level combination per partition (such as CALENDAR_YEAR and PROD_TOTAL).

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 theOracle9i 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. Fact 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;