Database Table Reorganization Using Virtual Partitions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face challenges in reorganizing database tables with concurrent updates, requiring quiescing the entire database and allocating excessive memory, which can lead to downtime and memory shortages, especially with large table sizes.
Innovation Solution
The method employs virtual partitions to allow online reorganization of database tables with concurrent updates by creating union views and allocating memory incrementally, freeing memory as each virtual partition is completed, thus minimizing memory usage and avoiding quiescing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the entire database is quiesced for reorganization, then data consistency is ensured, but system availability and productivity are lost
Solution Approach 1:
The database table is divided into virtual partitions that can be reorganized independently. Instead of quiescing the entire database, the system processes one virtual partition at a time while maintaining access to other partitions through union views, thus ensuring data consistency for each partition while preserving overall system availability
Solution Approach 2:
Union views are introduced as intermediary structures that allow concurrent access to reorganized and unreorganized partitions simultaneously. These views act as mediators between the reorganization process and user queries, enabling data consistency in reorganized partitions while maintaining system availability through continuous access paths
2Quantity of substance
If memory is allocated for the entire table during reorganization, then complete data redistribution is enabled, but memory resources are exhausted causing system instability
Solution Approach 1:
Memory allocation is segmented to match the virtual partition structure. Instead of allocating memory for the entire table at once, the system allocates memory incrementally for each virtual partition being reorganized. This allows complete data redistribution capability while maintaining system stability by limiting peak memory usage to partition-level requirements
Solution Approach 2:
The reorganization process uses periodic action by deallocating memory for completed virtual partitions and allocating memory for the next partition to be reorganized. This cyclical pattern of allocation and deallocation enables full table reorganization while maintaining stable memory resources throughout the process
Data Source
AI summary
In an approach to online reorganization of database tables with concurrent updates using virtual partitions, responsive to receiving a table to reorganize, rows of the first table are selected, where the rows of the first table create a first virtual partition. A memory for a second virtual partition is allocated, where the second virtual partition is appended to a second table. A union view is created comprising a union of remaining rows of the first table with the second table, where the remaining rows of the first table have not been copied to the second table. The first virtual partition is copied into the second virtual partition. Responsive to receiving queries before copying the first virtual partition into the second virtual partition is complete, the queries are allowed to access the union view.