Performance issue of partitioned async MV

Working with Data
1

i am looking for some insight to understand and address the performance issue of refreshing a partitioned async MV. From what I read, MV with partition could speed up the performance in case of incremental refresh. In my case, my MV is expected to experience 2 kinds of refreshs, triggered by update of base tables:

  • frequent and periodic new data append in new partitions
  • occasional (less frequent) whole MV (all partitions) refresh
    besides
  • initial MV creation
    as well

The base table is still in small size, ~5K rows and ~50 partitions.

what observed is that, if the MV is NOT partitioned, the initial creation and whole MV refresh are fast enough, taken less than 1s. However, if the MV is partitioned, then, it becomes very slow, taking ~90s on initial creation and whole MV refresh. The slowing down seems to be because of the data volume (as the MV has merely ~5K rows of data) but proportional to the number of partitions (looks like each partition adds 1 or 2 seconds to the initial creation and whole MV refresh latency).

Because the table size is still such a tiny size, so we couldn’t judge how much benefit would come from partitioning the MV on incremental append when the base table grows large. However, the poor performance on initial creation and occasion whole MV refresh of such a small MV is way big than acceptable. The partition number are expected to grow up to ~1,000, i.e. 20 times more. If the initial creation and whole MV refresh is proportional to the number of partition, it would take ~1,800s (i.e. 30 minutes) just to complete the initial MV creation and occasion refresh.

Hence, it is a hard to decide whether this MV should be partitioned or not. If incremental refresh on partitioned and non-partitioned MV is a matter of 1 seconds vs 10 seconds, then, the benefit from partitioning the MV doesn’t worth its sacrifice at all (i.e. 30 minutes on initial creation or occasion whole MV refreshing).

I am looking for advise on:

  • why the initial creation and whole MV refresh on partitioned MV is so slow. Am I missing something in the setup? Or it is expected behavior?
  • how much difference it would make on incremental append new data refresh between partitioned and non-partitiond MV?

Thanks!