|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
Creating Dimension Materialized Views , 3 of 5
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 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 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 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
tblspace_namePCTFREE 0 COMPUTE STATISTICS LOCAL NOLOGGING;
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.
SELECT statement contains a
COUNT(*) function, a
MAX aggregate functions, and a
ROLLUP function. The following example shows the basic syntax.
CREATE MATERIALIZED VIEW
mv_namePARTITION 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(
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.