Oracle9i OLAP User's Guide Release 2 (9.2) Part Number A95295-01 |
|
Creating Fact Materialized Views With OLAP Summary Advisor , 3 of 5
The scripts generated by OLAP Summary Advisor 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 OLAP Summary Advisor.
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 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 TABLEmv_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 TABLEmv_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 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 INDEXindex_name
ONmv_name
(mv_colname
) TABLESPACEtblspace_name
PCTFREE 0 COMPUTE STATISTICS LOCAL NOLOGGING;
|
![]() Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|