Online Index Building with Point-in-Time Consistency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Building indexes in large relational databases is time-consuming, leading to undesirable read-only access restrictions due to the significant time required for index creation, which can impact database performance and availability.
Innovation Solution
A method that creates a consistent image of source data at a point-in-time, updates the index incrementally by applying log records, and locks access only when the index is substantially caught up with current changes, allowing for minimal downtime and maintaining database availability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional index building methods are used, then the index is built completely before allowing access, but the time required to build the index is significant and enforces read-only access to source data
Solution Approach 1:
The patent creates a preliminary consistent image of the source data at a specific point-in-time before index building begins. This preliminary action captures the state of the data, allowing the index to be built from this snapshot while the system continues to accept updates, thereby reducing the time source data is locked without compromising the ability to create a consistent index.
Solution Approach 2:
The patent segments the index building process into multiple phases: creating a consistent image at point-in-time, building the index incrementally from this image, and applying subsequent updates. This segmentation allows the index construction to proceed in parallel with data updates, reducing overall index building time while maintaining consistency through the use of the initial snapshot.
2Reliability
If read-only access is enforced during index building, then index consistency is maintained, but database availability and performance are impacted
Solution Approach 1:
By creating a consistent image of the source data before index building begins, the patent establishes a stable baseline that can be used for index construction without requiring the source data to remain locked. This preliminary capture of data state allows readers to continue accessing updated data while the index is built from the snapshot, maintaining database availability while ensuring index consistency.
Solution Approach 2:
The patent creates a copy of the source data at a specific point-in-time to serve as the basis for index building. This copying approach allows the index construction process to work with a static snapshot while the original source data remains accessible and updatable, thereby maintaining database availability without compromising index consistency.
3Measurement precision
If indexes are rebuilt completely after LOAD operations, then the index reflects current data state, but the time required increases significantly
Solution Approach 1:
The patent creates a consistent image at the point-in-time when loading begins, establishing a baseline for the index. After the LOAD operation completes, the system applies only the updates that occurred during the load process to this baseline image, rather than rebuilding the entire index from scratch. This approach ensures the index accurately reflects the current data state while significantly reducing the time required compared to a complete rebuild.
Solution Approach 2:
Instead of performing a complete index rebuild after LOAD operations, the patent applies only the necessary updates to the pre-existing consistent image. This partial action approach—updating only what changed during the load process—maintains index accuracy while reducing the time and resources required compared to rebuilding the entire index structure.
Data Source
AI summary
An online index building operation is disclosed for building an index from source data with minimal loss of availability to the source data. The source data can be maintained in a relational database system, such as in a tablespace of a DB2® environment. The disclosed operation creates a consistent image of the source data as of a point-in-time and creates an index from the consistent image. Then, the disclosed operation repeats the acts of making the image consistent as of a subsequent point-in-time and updating the index to reflect the subsequent consistent image until substantially caught up with the current changes to the source data. If not caught up, the disclosed operation continues unless it is falling behind at which point the operation terminates. If it is caught up, the disclosed operation locks access to the source data, updates the image to reflect any final changes, updates the index, and allows access to the index.