Effects of Partition Cardinality On Query Performance
Multilevel partitioning typically defines a large number of partitions for the combined partitioning expression.
If there is a large number of populated partitions for the combined partitioning expression (high partition cardinality-note that the term cardinality here does not refer to the number of rows per partition, but the number of partitions themselves), then performance of primary index access, joins, and aggregations on the primary index can be degraded. This can be extremely critical given that the maximum number of combined partitions that can be defined is 263-1.
Therefore, multilevel partitioning may be an appropriate choice when these operations are rarely done without also obtaining significant row partition elimination.
Note, however, that having a large number of row partitions making up the combined partitioning expression reduces the number of data blocks the system must process when it needs to access only a small number of row partitions due to row partition elimination.