Database Index Reorganization via Backup Restore Alignment
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face performance issues due to fragmentation in B+ trees, which occurs from split and join operations, leading to a mismatch between logical and physical orders of leaf pages, resulting in inefficient data retrieval and storage.
Innovation Solution
A method for reorganizing a source index tree into a target index tree by backing up and restoring the index tree while maintaining the logical order, constructing new leaf and non-leaf pages, and rebuilding non-leaf pages during the restore process, ensuring the target index tree's physical order aligns with its logical order.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If database indices are updated frequently through split and join operations, then the index structure adapts to data changes, but fragmentation occurs causing mismatch between logical and physical order of leaf pages
Solution Approach 1:
The patent performs a preliminary full table scan during backup to read all index records in logical order, and pre-constructs the target index structure with correct physical ordering before restore operations. This preliminary organization prevents fragmentation that would otherwise occur during frequent update operations.
Solution Approach 2:
The patent creates a copy of the source index tree during backup, then reconstructs this copy with optimized physical ordering during restore. By working with a copy rather than the live index, the system can reorganize the physical structure without affecting ongoing database operations, thereby maintaining both adaptability and stability.
2Stability of the object's composition
If full reorganization of index tree is performed after restore, then physical order aligns with logical order, but the duration of backup and restore operations increases significantly
Solution Approach 1:
The patent performs the reorganization action during the backup phase by reading all index records in logical order and constructing target leaf pages with correct physical ordering. This preliminary reorganization eliminates the need for time-consuming post-restore reorganization, thereby reducing total backup and restore duration while maintaining proper physical-logical alignment.
Solution Approach 2:
The patent merges the reorganization function into the backup and restore process itself. By combining index reading, physical reordering, and target structure construction into a single integrated operation during backup, the system achieves both physical-logical alignment and time efficiency without requiring separate reorganization steps.
3Productivity
If index records are stored in physical order rather than logical order, then storage efficiency improves, but data retrieval performance deteriorates due to fragmentation
Solution Approach 1:
The patent applies different ordering strategies to different parts of the index structure. Leaf pages are organized with physical order matching logical order through the backup/restore process, while internal nodes maintain their hierarchical structure. This local optimization ensures both storage efficiency and retrieval speed by eliminating fragmentation at the leaf level where it impacts performance most.
Solution Approach 2:
The patent creates a reconstructed copy of the index during restore with optimized physical ordering that matches logical order. This copy eliminates the fragmentation present in the original physically-stored index while maintaining the efficient hierarchical structure needed for fast retrieval, thereby achieving both storage efficiency and speed.
Data Source
AI summary
A mechanism is provided for reorganizing a source index tree of a database table resulting in a target index tree of the database table is provided. A backup process reads source leaf pages of the source index tree in a logical order defined by a sequence of index keys. The logical order can be different from a physical order of the source leaf pages defined by a sequence of respective storage locations. The backup process constructs target leaf pages of the target index tree maintaining the logical order. A restore process constructs target non-leaf pages of the target index tree based on the distribution of the index keys among the constructed leaf pages and stores the target leaf and non-leaf pages.