Considerations / Limitation on PGB and PBR

 

Partition by growth has some limitations that should be considered when deciding whether to use this partitioning method:

 

1. Increased complexity: Partition by growth can add complexity to the database design, management, and maintenance. It requires a good understanding of the data, the access patterns, and the growth patterns of the table.
2. Performance impact: Partition by growth can have an impact on the performance of the database. The creation of new partitions and the maintenance of historical partitions can cause increased I/O and CPU overhead.
3. Storage limitations: Partition by growth can require a large amount of storage, especially if a large number of historical partitions are retained. This can increase the cost of storage and make it harder to manage the data.
4. Query complexity: Partition by growth can make queries more complex, especially when querying historical partitions. The query optimizer may have to search multiple partitions to find the required data, which can slow down query performance.
5. Archiving and purging: Partition by growth requires the archiving and purging of historical partitions to keep the table size manageable. This process can be time-consuming and requires additional resources.
6. Limited scalability: Because partition by growth is based on predefined thresholds, it may not be able to adapt to unexpected changes in the growth rate of the data.

It's important to note that partition by growth is not a suitable partitioning method for all use cases. It's best suited for tables that are subject to high levels of insert activity and are expected to grow over time. It's important to weigh the benefits of partition by growth against the limitations and costs before deciding to use this partitioning method.

Partition by range also has some limitations that should be considered when deciding whether to use this partitioning method:

1. Limited query optimization: Partition by range can make it difficult for the query optimizer to determine the best partition to access when executing a query, especially when the query includes a range of values that spans multiple partitions.
2. Maintenance complexity: Partition by range requires the maintenance of the partitioning key and the partitioning range. This can be time-consuming and requires additional resources, especially when new partitions need to be added or when the partitioning range needs to be adjusted.
3. Limited scalability: Partition by range can be limited by the number of partitions that can be created and the size of the partitioning range. This can make it difficult to scale the table as the data grows.
4. Limited data types: Partition by range is limited to certain data types, such as numeric and date data types, which means it can't be used on other data types such as varchar or clob.
5. Data skew: Partition by range can lead to data skew, where some partitions contain significantly more data than others. This can cause performance issues and make it difficult to balance the workload across the partitions.
6. Limited flexibility: Partition by range is based on a predefined partitioning key and partitioning range, which can limit the flexibility of the partitioning scheme.

It's important to note that partition by range is not a suitable partitioning method for all use cases. It's best suited for tables that need to be queried or searched based on specific values or ranges of values. It's important to weigh the benefits of partition by range against the limitations and costs before deciding to use this partitioning method.