Setting Partition Mode To Finite For All Events (Doc ID 2155831.1)

Last updated on JULY 01, 2024

Applies to:

Goal

On : 7.5.0.14.0 version, Purging

For performance reasons, the user is considering to store all events in the monthly partitions.
As part of this effort, partition_mode would be set to 1 for all events (is_partitioned is already set). With this setting, it has been validated that all events are now going to the right monthly partition.

Events currently in the partition historic will be updated to contain a POID that confirms to the partition based POID logic. Any other object referencing these events will be updated as well.

The user is not using the out-of-box BRM purging functionality as purging requirements are too complex. Purging is currently being done via custom scripts.

Customized archive and compression approach based on poid_types approved by the business and the development team. The POID types currently in the partition historic are not included in custom purge by definition of their event type. Rows that cannot be purged are compressed to reduce storage consumption.

Another technique is used when rebuilding partitions is to order the data by ACCOUNT_OBJ_ID0. This improves the compression ratio and significantly reduces query IO as only a few blocks per partition must be visited to get all applicable data. This is more effective when a partition is static (unlike historic).

The mammoth partitions results in skew when performing partition-wise joins for reports and prolongs index range scans which require historic. Removing historic will allow better and more completely manage purge and compression.

Given these conditions, are there any side effects in stopping to use the historic event partition?
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.