Database Index Maintenance via Status Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Maintaining indexes in large database systems is inefficient, leading to performance degradation during data modification operations due to the need for synchronous updates of all indexes, which can be time-consuming and resource-intensive, especially when some indexes are only periodically needed.
Innovation Solution
Implementing a status-based index maintenance system that categorizes indexes as current, stale, or deferred, allowing for asynchronous updates of stale indexes and on-demand building of deferred indexes, reducing the need for synchronous maintenance and optimizing resource allocation based on query requirements.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If all indexes are maintained synchronously with table modifications, then index currency is improved, but data modification performance deteriorates
Solution Approach 1:
The patent segments indexes into two categories: current indexes that require synchronous maintenance with table modifications, and aged indexes that use asynchronous maintenance. This segmentation allows the system to maintain currency for critical indexes while allowing less critical indexes to be updated asynchronously, thereby improving overall data modification performance while preserving necessary index currency.
Solution Approach 2:
The patent introduces dynamic status transitions for indexes between current and aged states. The query optimizer dynamically determines whether to use a current or aged index based on query characteristics and system state. This dynamic approach allows the system to adapt index maintenance strategies to actual workload requirements, improving performance while maintaining index currency only when necessary.
2Speed
If multiple indexes are created on the same table, then query efficiency is improved, but index maintenance overhead increases
Solution Approach 1:
The patent segments the set of indexes on a table into current and aged categories. Current indexes are maintained synchronously and can be used by the query optimizer for immediate query processing. Aged indexes are maintained asynchronously and represent a form of batched maintenance. This segmentation reduces the immediate maintenance overhead for multiple indexes while preserving query efficiency through available current indexes.
Solution Approach 2:
The patent implements periodic background processes that refresh aged indexes asynchronously. Instead of maintaining all indexes continuously in real-time, the system uses periodic background maintenance for aged indexes, reducing the immediate time overhead during data modifications while still providing index access capabilities when needed.
3Loss of energy
If indexes are maintained manually just-in-time, then resource consumption is reduced, but operational complexity increases
Solution Approach 1:
The patent implements a self-service mechanism where the query optimizer automatically determines whether to use current or aged indexes based on query characteristics and system state. The system autonomously manages index status transitions and maintenance timing without requiring manual intervention, thereby reducing operational complexity while optimizing resource consumption through intelligent index selection and maintenance scheduling.
Solution Approach 2:
The patent incorporates feedback mechanisms where the query optimizer monitors query patterns, system workload, and index usage statistics to dynamically adjust index maintenance strategies. This feedback-driven approach allows the system to automatically optimize resource consumption by maintaining indexes only when and where they provide value, eliminating the need for manual just-in-time index creation while reducing operational complexity.
Data Source
AI summary
Methods and apparatus, including computer program products, for maintaining a set of indexes in a database management system (DBMS) having at least one table. A current, stale or deferred status is defined for at least a part of the indexes, resulting in at least a part of a set of current, stale, or deferred indexes in the DBMS. Current indexes are maintained by refreshing a current index synchronously with a table change relating to the current index. Stale indexes are maintained by refreshing a stale index continuously and asynchronously to table modifications of tables relating to the stale index based on log information relating to the modifications. Deferred indexes are maintained by building a deferred index in response to a query to a table relating to the deferred index, thereby bringing the deferred index in accordance with the current query time status to the table relating to the deferred index.