CODEWITHSUNDEEP
oracle-databasedata-warehousepartitioningmaterialized-views
user5626202
user5626202

Reputation:

Oracle Materialized views: partition strategy

I have a summary Materialized View organized by year (char), month (char) and 3 other dimensions, followed by many measures. My data spans from 2013 to present time and continuosly increment.

Since the underlying fact table is huge (500+M rows incremented 100+M every year) I'm considering to partition both the table and the MV in order to refresh just the last 12 months in the MV.

After many readings I've supposed to partition the fact table by range (every month) and partition the MV by month and subpartition it by year.

I know that the partition strategy is crucial for performances so I'm asking if somebody has a better solution or the one I've worked out is optimal.

Thank you!

Example of code of the MV partitioning:

CREATE MATERIALIZED VIEW my_mv
    PARTITION BY LIST (month) SUBPARTITION BY LIST (year)
        ( PARTITION p01 VALUES ('01')
          ( SUBPARTITION p_0117 VALUES ('2017')
            , SUBPARTITION p_0116 VALUES ('2016')
            , SUBPARTITION p_0115 VALUES ('2015')
            , SUBPARTITION p_0114 VALUES ('2014')
            , SUBPARTITION p_0113 VALUES ('2013')
          )
        , PARTITION p02 VALUES ('02')
          ( SUBPARTITION p_0217 VALUES ('2017')
            , SUBPARTITION p_0216 VALUES ('2016')
            , SUBPARTITION p_0215 VALUES ('2015')
            , SUBPARTITION p_0214 VALUES ('2014')
            , SUBPARTITION p_0213 VALUES ('2013')
          )

        ...

        , PARTITION p12 VALUES ('12')
          ( SUBPARTITION p_1217 VALUES ('2017')
            , SUBPARTITION p_1216 VALUES ('2016')
            , SUBPARTITION p_1215 VALUES ('2015')
            , SUBPARTITION p_1214 VALUES ('2014')
            , SUBPARTITION p_1213 VALUES ('2013')
          )
        )
AS
SELECT
   ...
FROM
   ...
;

Upvotes: 1

Views: 3049

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59503

Why not making simple partition by months? It does not make any sense to make partition by month and subpartition by year. Dropping older partitions would not be possible, in general maintenance would be quite difficult.

SELECT
...
TO_TIMESTAMP(year||month, 'YYYYMM') AS PARTITION_KEY,
...

and then for you MView:

PARTITION BY RANGE (PARTITION_KEY) INTERVAL (INTERVAL '1' MONTH)

This also saves you from any partition maintenance.

Regarding performance it strongly depends on your main queries you run on it. It is fairly easy to have worse performance on partitioned table than on non-partitioned table. If your main queries do not select certain date ranges then partition on date is in contradiction (in terms of performance).

Upvotes: 1

Related Questions