User-Defined Index Rebuild via Row Identifier Mapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
User-defined indexes in database systems are expensive to rebuild during partition maintenance operations such as split, merge, and move, as they require complex computations and rebuild from scratch, which is inefficient compared to native indexes.
Innovation Solution
A system and method that uses row identifier mapping to efficiently rebuild user-defined index partitions during partition maintenance operations by storing and reusing mapping information, allowing parallel processing and reducing the need for full index rebuilds by replacing old row identifiers with new ones.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If user-defined indexes are rebuilt from scratch during partition maintenance operations, then index consistency is maintained, but the time and computational resources required increase significantly
Solution Approach 1:
The system performs preliminary actions by capturing row identifier mappings during the partition maintenance operation itself, before the index rebuild is needed. The mapping information is stored in a temporary table during the partition split/merge/move operation, so when the index needs to be rebuilt, the mapping data is already available, eliminating the need for time-consuming full index reconstruction
Solution Approach 2:
Instead of rebuilding the entire index from scratch, the system creates a simplified copy or representation of the index by replacing old row identifiers with new ones using the pre-captured mapping information. This copying approach maintains index consistency while dramatically reducing rebuild time and computational resources
2Manufacturing precision
If user-defined indexes are rebuilt from scratch during partition maintenance operations, then accurate index data is ensured, but computational complexity and resource consumption increase
Solution Approach 1:
The system introduces an intermediary mechanism - a temporary table storing row identifier mappings - that mediates between the partition maintenance operation and the index rebuild process. This intermediary captures the transformation of row identifiers during partition operations and provides this information to the index rebuild process, ensuring accuracy while simplifying the overall complexity
Solution Approach 2:
The system extracts only the essential information needed for index rebuilding - the row identifier mappings - from the partition maintenance operation. By taking out just this critical data and storing it separately, the system avoids the complexity of rebuilding the entire index structure while ensuring index data accuracy through the extracted mapping information
3Reliability
If full index rebuild is performed during partition maintenance, then index consistency is maintained, but productivity during maintenance operations decreases
Solution Approach 1:
The system performs preliminary actions by capturing row identifier mappings during the partition maintenance operation itself, before the index rebuild is needed. The mapping information is stored in a temporary table during the partition split/merge/move operation, so when the index needs to be rebuilt, the mapping data is already available, eliminating the need for time-consuming full index reconstruction
Solution Approach 2:
Instead of performing a complete index rebuild, the system applies partial action by only updating the row identifier mappings in the index using the pre-captured mapping information. This partial update approach maintains index consistency while dramatically improving productivity during partition maintenance operations
Data Source
AI summary
A method, system, and computer program product provides improved performance for rebuild of user-defined indexes during partition maintenance operations (partition split, merge, and move). A method of maintaining a index of a partitioned database table comprises performing a partition maintenance operation on the partitioned database table wherein a plurality of rows of the partitioned database table are moved from one partition to another, storing mapping information for at least some of the plurality of moved rows, including an old mapping for each of the moved rows and a corresponding new mapping for each of the moved rows, and when rebuilding the index, replacing an old mapping in the index for each of the plurality of moved rows with the corresponding stored new mapping for each of the plurality of moved rows.