One of the fact tables in the database consists of 650 million rows. It is partitioned by a date field - each partition consisting of 4 months. The fact table hold 38 months of data.

We are creating a Materialized View out of this fact table. There are 3 major dimensions, viz., Report Period (data type - VARCHAR2), Portfolio Group Id (data type - NUMBER) and Site Id (data type - NUMBER). Apart from the above there are 4 other dimensions. The materialized view would contain around 90 million rows.

The value sets for the 3 major dimensions would be:
Report Period - 38 report periods
Portfolio Group Id - 6 values
Site Id - 20 values

We are planning to partition the materialized views in the following manner:
Composite Partitioning with
- Range Partitioning on Report Period and Portfolio Group Id
- Sub-partitioning (List) on Site Id

Please put forth your views/inputs on this.

This is the framework of the Materialized View:

CREATE MATERIALIZED VIEW MV_TEMP
PARTITION BY RANGE (REPORT_PERIOD, PORTFOLIO_GROUP_ID)
SUBPARTITION BY LIST (SITE_ID)
SUBPARTITION TEMPLATE(
SUBPARTITION site_id1 VALUES (1, 2, 3, 4) TABLESPACE TBS_DATA,
SUBPARTITION site_id2 VALUES (5, 6, 7, 8) TABLESPACE TBS_DATA,
SUBPARTITION site_id3 VALUES (9, 10, 11, 12) TABLESPACE TBS_DATA,
SUBPARTITION site_id4 VALUES (13, 14, 15, 16) TABLESPACE TBS_DATA,
SUBPARTITION site_id5 VALUES (17, 18, 19, 20) TABLESPACE TBS_DATA)
(
PARTITION q1_1999 VALUES LESS THAN('2001-M08', 2),
PARTITION q2_1999 VALUES LESS THAN('2001-M08', 3),
PARTITION q3_1999 VALUES LESS THAN('2001-M08', 4),
...
PARTITION q4_1999 VALUES LESS THAN('2002-M08', 2),
PARTITION q5_1999 VALUES LESS THAN('2002-M08', 3),
PARTITION q6_1999 VALUES LESS THAN('2002-M08', 4),
...
... for 38 year_month period
PARTITION q19_1999 VALUES LESS THAN (MAXVALUE, MAXVALUE)
)

Thnx,