Overflow Row Repair in Relational Database Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for removing overflow rows from relational database tables often result in extended downtime, impacting database availability and performance, as they require locking the table during reorganization processes.
Innovation Solution
A method to dynamically identify and remove overflow rows from relational database tables without taking the table offline, using techniques such as inspecting physical storage, log files, and temporary storage to unload, delete, and reload identified overflow rows, while minimizing user impact by limiting access restrictions and processing intervals.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If prior art reorganization process is used to remove overflow rows, then database performance is improved, but table availability deteriorates due to extended locking
Solution Approach 1:
The patent segments the table into multiple partitions and processes overflow row removal in a distributed manner across different partitions simultaneously, rather than locking and processing the entire table at once. This allows partial availability during maintenance operations.
Solution Approach 2:
The patent implements periodic sampling of pages to identify overflow rows rather than continuous processing. The system periodically scans pages, identifies overflow rows, and removes them in intervals, allowing the table to remain accessible between maintenance cycles.
2Manufacturing precision
If table locking is implemented during reorganization, then overflow row removal is ensured, but user access is blocked
Solution Approach 1:
The patent introduces an intermediary mechanism where overflow row identification and removal operations are coordinated through a sampling and selection process rather than direct table locking. The system uses page sampling to identify candidate pages, then selectively processes overflow rows without requiring comprehensive table locks.
Solution Approach 2:
The patent performs partial action by processing only a sampled subset of pages containing overflow rows rather than the entire table. This partial processing approach removes sufficient overflow rows to improve performance while minimizing impact on table availability and user access.
3Measurement precision
If comprehensive page scanning is performed to identify all overflow rows, then detection accuracy is improved, but processing time increases
Solution Approach 1:
The patent performs preliminary sampling of pages to identify candidate pages containing overflow rows before conducting detailed processing. This preliminary action filters the large set of all pages down to a smaller subset of pages that actually contain overflow rows, reducing subsequent processing time while maintaining detection accuracy for the identified rows.
Solution Approach 2:
The patent uses partial sampling of pages rather than scanning every page in the table. By selecting a representative sample of pages to examine, the system achieves sufficient detection accuracy to identify and remove overflow rows without the time cost of comprehensive scanning of the entire table.
Data Source
AI summary
Techniques to repair overflow rows in a relational database table are described. In one implementation, a source table's physical memory pages are interrogated to identify overflow rows. In another implementation, overflow rows generated during database update operations are identified from non-table sources (e.g., log files). In general, overflow rows are repaired by: unloading one or more rows identified as overflow rows; locking the source table from outside access; disabling or deleting any associations associated with the unloaded rows; deleting the identified overflow/unloaded rows from the source table; reloading the previously unloaded rows back into the source table; restoring any associations previously disabled/deleted; and unlocking the tables. Rows may be processed in groups small enough not to significantly impact users accessing either the source or associated tables.