Dictionary-Based Archive Index for Database Storage Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional data archiving methods require maintaining indexes in transactional databases, which increases storage needs and maintenance costs, and makes it difficult to access archived data efficiently.
Innovation Solution
A computer-implemented method using a dictionary-based archive index that stores a subset or all attributes of archived data records outside the transactional database, allowing for faster and more flexible search and retrieval of archived data through a search engine-based indexing system.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If archived data is stored in a flat file outside the transactional database, then storage capacity is improved, but data access speed deteriorates
Solution Approach 1:
The system segments data into two parts: archived data stored in flat files and index data stored in the transactional database. This segmentation allows the bulk data to be stored externally for capacity while the index remains in the transactional database for fast access, resolving the contradiction between storage capacity and access speed.
Solution Approach 2:
An index structure acts as an intermediary between the transactional database and the archived flat files. The index contains key-value pairs where keys are search criteria and values are pointers to the archived data locations. This intermediary enables fast searching without requiring direct access to the large flat files, thus maintaining access speed while improving storage capacity.
2Ease of operation
If an index to the archive is maintained outside the archive, then data access is improved, but device complexity increases
Solution Approach 1:
The system merges the index maintenance function into the existing transactional database operations. When data is inserted, updated, or deleted in the transactional database, the corresponding index entries are automatically updated as part of the same transaction. This merging eliminates the need for separate index maintenance mechanisms, reducing system complexity while maintaining ease of data access.
Solution Approach 2:
The transactional database serves multiple functions: it stores active data, maintains the archive index, and manages the mapping between active and archived data. This multi-functionality reduces the need for separate dedicated index structures, simplifying the overall system architecture while preserving fast data access capabilities.
3Reliability
If the index is stored in the transactional database, then data consistency is improved, but storage requirements increase
Solution Approach 1:
The system extracts the bulk archived data from the transactional database and stores it in external flat files, keeping only the essential index information in the database. This extraction reduces the storage requirements in the transactional database while maintaining data consistency through the index, which remains under the database's consistency management.
4Ease of manufacture
If traditional archiving methods are used, then implementation simplicity is improved, but maintenance costs increase
Solution Approach 1:
The system implements self-service archiving where the transactional database automatically manages the index updates as part of its normal operations. When records are inserted, updated, or deleted, the corresponding index entries are automatically maintained without requiring external intervention. This self-service approach maintains implementation simplicity while reducing maintenance costs by eliminating separate index management processes.
Data Source
AI summary
Methods and apparatus, including computer program products, for archiving data from a database. One method includes identifying a data record to be archived; determining the contents of an archive record, the archive record having values for a first plurality of attributes in the data record; storing the archive record in a data archive; determining the contents of an index record, the index record comprising values for a second plurality of attributes in the data record; adding the index record to a dictionary-based archive index with a reference to the location of the archive record in the data archive; deleting the data record from the database; accepting a query for a desired archive record; and performing a search of the archive index to find the desired archive record.