Reorganizing Partition-by-Growth Databases with LOB Columns
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Partition-by-growth (PBG) databases with large object (LOB) columns face inefficiencies in reorganization due to the inability to maintain clustering order, leading to slower data access and increased processing time, especially when conventional reorganization processes fail to move LOB rows between partitions.
Innovation Solution
An efficient reorganization process that includes data verification, parallel reorganization of LOB auxiliary tables with the base table, and concurrent writing of LOB rows, using a partition assignment table to ensure LOB rows change partitions, allowing the database to remain available for read-write access and achieve full clustering order.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional reorganization processes are used for PBG databases with LOB columns, then the database can be reorganized within each partition, but the data cannot be fully reorganized into clustering order across partitions and processing time is excessive
Solution Approach 1:
The patent merges the reorganization of base tables and LOB auxiliary tables into a single parallel process. The base table reorganization and LOB auxiliary table reorganization are performed simultaneously using the same partition assignment table, eliminating the need for separate sequential operations. This combining of operations significantly reduces total processing time while achieving full clustering order across partitions.
Solution Approach 2:
The patent performs preliminary actions by creating the partition assignment table before actual reorganization begins. This assignment table pre-determines the target partition for each row based on clustering index values, allowing the reorganization process to proceed efficiently without dynamic decision-making during data movement. The preliminary preparation enables parallel processing to occur smoothly.
2Reliability
If LOB rows are reorganized sequentially after base table reorganization, then data integrity is maintained, but the total reorganization time increases significantly
Solution Approach 1:
The patent combines base table reorganization and LOB auxiliary table reorganization into a single parallel operation. Both table types are reorganized simultaneously using coordinated processes that read from the base table and write to corresponding LOB auxiliary tables in parallel, maintaining data integrity through the use of a shared partition assignment table while dramatically improving throughput.
Solution Approach 2:
The patent ensures continuous useful action by eliminating idle time between base table reorganization and LOB auxiliary table reorganization. Both processes run continuously and simultaneously, with no gaps or sequential waiting periods. This continuous parallel operation maximizes resource utilization while maintaining data consistency through coordinated processing.
3Reliability
If the database is taken offline for reorganization, then data consistency is ensured, but availability is lost and updates cannot be applied
Solution Approach 1:
The patent performs preliminary actions by creating the partition assignment table and determining all reorganization targets before the actual data movement begins. This pre-planning allows the reorganization to proceed with minimal disruption, as the database can remain online and accept updates throughout the process. The preliminary preparation eliminates the need for extended offline periods.
Solution Approach 2:
The patent introduces dynamics by allowing the database to transition between states during reorganization. The database can remain online and accept read-write operations while reorganization progresses, with the system dynamically managing data movement and update application. This dynamic approach maintains availability while ensuring eventual data consistency.
4Reliability
If LOB rows are written after partition determination, then data integrity is maintained, but processing time increases due to sequential operations
Solution Approach 1:
The patent performs preliminary action by determining the output LOB dataset for each LOB row before writing begins. The system pre-calculates which partition and dataset each LOB row should be written to based on the partition assignment table, eliminating the need for dynamic decision-making during the write operation. This preliminary determination enables efficient parallel writing while maintaining data integrity.
Solution Approach 2:
The patent merges the LOB row writing process with the base table reorganization process. Both operations proceed in parallel, with LOB rows being written to their destination partitions simultaneously as base table rows are being moved. This combined approach maintains data integrity through coordinated processing while significantly reducing total processing time compared to sequential operations.
Data Source
AI summary
Systems and methods are provided for reorganizing a partition-by-growth database with LOB columns. An example method includes creating a partition assignment table by performing a logical reorganization of the partition-by-growth database, wherein an assignment of at least one base table row and its corresponding LOB in the partition assignment table differs from a current assignment of the at least one base table row. The method also includes performing a base table reorganization based on the partition assignment table in parallel with a LOB auxiliary table reorganization based on the partition assignment table and a LOB dataset assignment table. The method may also include applying changes to the reorganized base tables and the reorganized LOB auxiliary tables using the partition assignment table.