Virtual Archiving Database Records Single Table Query
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database-management systems (DBMSs) face inefficiencies in managing archived and active data, as they require dividing data across multiple partitions, leading to increased complexity and resource consumption when querying both types of data, especially due to the need for resource-intensive operations like UNION ALL when combining archived and non-archived data.
Innovation Solution
Implementing a virtual archiving system within a DBMS that stores both archived and non-archived data in a single table by adding an 'Archived' field to each record, allowing queries to distinguish between active and archived data without physically moving records, thus eliminating the need for combining separate partitions during queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is divided into multiple partitions for archiving, then archived and active data can be stored separately, but query complexity and resource consumption increase when combining data from multiple partitions
Solution Approach 1:
The patent applies segmentation by dividing the database table into virtual partitions based on the Archived field value. Records are logically segmented into active (Archived=0) and archived (Archived=1) groups within the same physical table structure, allowing separate management while maintaining unified storage
Solution Approach 2:
The patent merges multiple logical partitions (active and archived data) into a single physical table structure. By using a unified table with an Archived field rather than separate tables or partitions, the system combines storage resources while maintaining logical separation through the field value, eliminating the need for complex UNION ALL operations when querying both data types
2Loss of energy
If separate archive repositories are used, then storage costs are reduced, but storage redundancy increases due to maintaining multiple data copies
Solution Approach 1:
The patent merges archived and active data into a single physical storage location (one table), eliminating redundant storage structures. The unified table approach ensures each record exists in exactly one location with an Archived field indicator, preventing the storage redundancy that occurs when maintaining separate physical copies in distinct archive repositories
3Reliability
If records are physically moved to archive partitions, then archived data is stored efficiently, but query performance degrades due to the need to join multiple partitions
Solution Approach 1:
The patent segments data logically rather than physically by using the Archived field to distinguish record types within the same table. This segmentation approach maintains physical proximity of all records while enabling logical separation for archiving purposes, avoiding the performance penalty of physical redistribution
Solution Approach 2:
The patent combines all records into a single physical table structure, eliminating the need for JOIN operations or UNION ALL queries that would be required when data is physically distributed across multiple partitions. The Archived field provides the necessary differentiation without requiring physical separation, thus maintaining optimal query performance
Data Source
AI summary
A database-management system (DBMS) archives a record of a database table by updating the record's unique “Archived” field. This indicates that the record should be considered to have been archived despite the fact that the record has not been physically moved to a distinct archival storage area. When a query requests access to the table, the DBMS determines whether the query requests access to only archived data, only active data, or both. If both, the DBMS searches the entire table. Otherwise, the DBMS scans each record's Archived field to consider only those records that satisfy the query's requirement for either archived or active data. If the DBMS incorporates Multi-Version Concurrency Control (MVCC) technology, the DBMS combines this procedure with MVCC's time-based version-selection mechanism.