|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
Creating Fact Materialized Views With DBMS_ODM, 3 of 9
The scripts generated by
DBMS_ODM.CREATEFACTMV_GS include syntax for partitioning, gathering statistics, and creating bitmap indexes.
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
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
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.
mv_nameCOMPUTE STATISTICS; EXECUTE dbms_stats.gather_table_stats (
mv_name, degree=> dbms_stats.default_degree,method_opt=>'for all columns size skewonly') ; ALTER TABLE
mv_nameMINIMIZE 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 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.