Database Archival Using Invisible Index Swapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The archival of historical data in relational database management systems (RDBMS) is a time-consuming process due to row-by-row data identification and movement, leading to performance issues, lengthy business outages, and high resource utilization, with existing methods requiring extensive human intervention and causing index unavailability and contention.
Innovation Solution
A method involving the creation of an invisible index for each primary index, block copying of data, and swapping indexes to minimize downtime, allowing for efficient archival of data in blocks rather than records, thereby reducing manual effort and resource consumption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If row-by-row data identification and movement is used for archival, then data can be archived with existing SQL or PL/SQL programs, but the archival process becomes time-consuming and lengthy
Solution Approach 1:
The patent replaces the mechanical row-by-row data processing approach with a block-level copy mechanism. Instead of using SQL or PL/SQL programs to identify and move data row by row, the system uses database block copy functionality to replicate entire data blocks at the storage level, dramatically reducing archival time while maintaining ease of implementation through automated block-level operations.
Solution Approach 2:
The patent segments the archival process into distinct phases: creating invisible indexes on data that will remain in the main table, identifying blocks to be archived, copying those blocks to archive tables, and swapping indexes. This segmentation allows parallel processing and optimizes each phase independently, reducing overall archival time while keeping the system manageable.
2Productivity
If row-by-row data processing is used for archival, then data can be moved to archive location, but CPU consumption and memory utilization increase significantly
Solution Approach 1:
The patent substitutes application-level row-by-row processing with database-engineered block-level copy operations. The database engine handles block copying at the storage level using optimized I/O paths, bypassing the need for extensive CPU and memory resources that would be required for row-by-row processing at the application level.
Solution Approach 2:
The patent uses block-level copying to replicate data blocks from the main table to archive tables. This copying mechanism operates at the storage level rather than the application level, enabling efficient data duplication with minimal CPU and memory overhead compared to row-by-row processing.
3Reliability
If data is locked for the entire archival process, then data consistency is maintained, but contention issues occur for other applications
Solution Approach 1:
The patent segments the archival process to minimize locking duration. Instead of locking the entire table for the duration of the archival operation, the system uses block-level locking only during the critical index swap phase. The block copy operation itself can proceed with minimal or no locking, allowing other applications to continue accessing the main table while archival occurs in the background.
Solution Approach 2:
The patent introduces invisible indexes as an intermediary mechanism. These invisible indexes are created on the main table pointing to data that will remain, allowing the system to swap between visible and invisible indexes without requiring prolonged table locks. This intermediary enables smooth transitions during archival while maintaining application accessibility.
4Reliability
If indexes are rebuilt during archival, then indexes remain usable, but the outage window is prolonged
Solution Approach 1:
The patent performs preliminary actions by creating invisible indexes in advance on the data that will remain in the main table after archival. These invisible indexes are built before the archival operation, so when the index swap is needed, the new indexes are already ready to go, eliminating the need for time-consuming index rebuilding during the archival process.
Solution Approach 2:
The patent uses invisible indexes as an intermediary to maintain index usability during archival. Instead of rebuilding indexes during the archival process, the system swaps between visible and invisible indexes. The invisible indexes serve as a pre-prepared intermediary that allows seamless transition without prolonging the outage window.
Data Source
AI summary
Archiving data in a relational database management system through a process in which an invisible index is created for each primary index of a data. When an input is received to initiate block copy of data to be archived; the data tables are locked and the identified blocks of data form the data table are block copied to an archive table. The invisible index is updated to correspond to the data table which had data archived. The primary index is then swapped with the invisible index and the data tables are unlocked. Data blocks from the main table which were copied into the archival table is deleted along with the old primary index.