Temporal Database Indexing via Time-Period Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face limitations in efficiently keeping track of historical data without relying on redo logs, which can be storage-constrained and require extensive reading, making it difficult to perform temporal queries on tables with time-related columns.
Innovation Solution
Implementing a relational database management system with an index that groups rows by time periods, using a B+ index organized table and partitioning to optimize temporal queries, allowing for efficient retrieval of data without depending on redo logs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If redo logs are used to keep track of historical data, then data integrity and rollback capability are improved, but storage space is consumed and query performance deteriorates due to extensive reading requirements
Solution Approach 1:
The patent segments the historical data storage by creating separate index structures (B+ trees) for different time periods and tables. Instead of using a single redo log for all historical data, the system divides the storage into multiple organized segments that can be independently accessed and managed, reducing the storage overhead required for maintaining historical data integrity.
Solution Approach 2:
The patent introduces an intermediary indexing structure (B+ tree) that mediates between the current database state and historical data. This intermediary layer provides efficient access to historical data without requiring direct reading of extensive redo logs, thus maintaining data integrity while reducing storage consumption and improving query performance.
2Loss of information
If redo logs are used to track all changes, then historical data retrieval is possible, but processing time increases due to extensive reading requirements
Solution Approach 1:
The patent applies preliminary action by pre-organizing historical data into B+ tree index structures as changes occur. Instead of reading through extensive redo logs during temporal queries, the system has already sorted and indexed the historical data in advance, enabling fast retrieval without extensive reading operations during query execution.
Solution Approach 2:
The patent replaces the mechanical sequential reading process of redo logs with a more efficient data structure (B+ tree) that enables logarithmic time complexity for data retrieval. This substitution transforms the query mechanism from linear scanning to structured index-based access, dramatically reducing processing time for temporal queries.
3Productivity
If data is organized for efficient temporal queries, then query performance is improved, but system complexity increases
Solution Approach 1:
The patent applies universality by making the B+ tree index structure serve multiple functions: it maintains current data state, stores historical data, and enables efficient temporal queries. This multi-functional approach improves query performance without proportionally increasing system complexity, as the same structural mechanism handles multiple operational requirements.
Data Source
AI summary
Techniques for history enabling a table in a database system so that past versions of rows of the history-enabled table are available for temporal querying. The table is history enabled by adding a start time column to the table and creating a history table for the history-enabled table. The history table's rows are copies of rows of the history-enabled table that have changed and include start time and end time fields whose values indicate a period in which the history table's row was in the history-enabled table. Temporal queries are performed on a view which is the union of the history-enabled table and the history table. The temporal queries are speeded up by period of time indexes in which the leaves are grouped based on time period size, identifiers are assigned to the groups, and the keys of the index include the group identifiers.