B-Tree Secondary Index Updates via Audit-Trail Checkpoints
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database search mechanisms, particularly in relational databases, face inefficiencies when searching on non-primary key columns, leading to unacceptably long search times due to brute force searches and the limitations of creating secondary indexes, which often require locking the entire table during creation, preventing updates for extended periods.
Innovation Solution
A method and system for updating a secondary index in a database by opening an audit trail, capturing system time, and applying subsequent updates to the index without locking the table, allowing for concurrent processing and efficient index creation and maintenance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If a secondary index is created to accelerate search for non-primary key columns, then search time is reduced, but the entire table must be locked during index creation, preventing updates for an unacceptably long period
Solution Approach 1:
The patent captures the current system time and uses it as a checkpoint to identify which audit trail records have already been applied to the secondary index. This preliminary action allows the system to resume index updates from where they left off without reprocessing all records, enabling the index to be built incrementally without locking the table.
Solution Approach 2:
The patent segments the index creation process into manageable units by processing audit trail records in committed sets rather than all at once. The system reads and applies records in batches, making progress visible through the captured system time checkpoint, which allows concurrent access without requiring the entire table to be locked.
2Reliability
If the entire table is locked during secondary index creation, then index consistency is ensured, but the table cannot be updated or used for an unacceptably long period
Solution Approach 1:
The patent introduces an intermediary mechanism - the captured system time checkpoint stored in control information - that mediates between the need for index consistency and table availability. This checkpoint allows the system to verify which audit trail records have been applied without requiring locks, ensuring consistency while maintaining table accessibility.
Solution Approach 2:
The patent enables continuous useful action by allowing the secondary index to be updated incrementally as audit trail records are committed, rather than requiring a single continuous locked operation. The captured system time allows the index building process to pause and resume without losing consistency, maintaining both reliability and availability.
3Device complexity
If a brute force search is performed on non-primary key columns, then no secondary index is needed, but search time becomes unacceptably long for tables with tens of thousands or millions of rows
Solution Approach 1:
The patent makes the secondary index dynamic by allowing it to be updated incrementally through audit trail processing rather than requiring static bulk creation. The captured system time checkpoint enables the index to evolve continuously, adapting to data changes without requiring complex static structures or lengthy creation processes.
Data Source
AI summary
A processor-based method of updating a secondary index for a B-tree in a database in a DBMS to include subsequent changes that occurred during creation of the secondary index includes opening an audit trail and moving back in the audit trail to a captured system time; making the secondary index visible to the DBMS for INSERT, UPDATE and DELETE processing; reading a next committed set of database updates from the audit trail; creating a secondary index modification for each row on each audit trail update and applying it to the secondary index; clearing the captured system time from control information for the B-tree to indicate completion; and making the secondary index visible to the DBMS for all processing.