Database Statistics Update via Standby Validation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In distributed database systems, statistical information updates often fail to improve query efficiency and optimization, leading to unnecessary resource conflicts and inefficiencies, especially during peak hours, and may even degrade performance.
Innovation Solution
A method that performs statistics updates on a secondary database first, testing for query efficiency and optimization improvements before applying the updates to the primary database, thereby avoiding unnecessary updates and optimizing the update strategy based on data changes, read/write frequency, and performance metrics.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If statistics update operations are performed on the primary database, then database statistical metadata is updated to potentially improve query efficiency, but resource conflicts and performance degradation occur during peak hours
Solution Approach 1:
The patent performs statistics update operations on a secondary database copy before applying them to the primary database. This preliminary action allows the system to test and validate statistics updates without interfering with primary database operations during peak hours, thus maintaining query execution efficiency while still updating statistical metadata when beneficial
Solution Approach 2:
The secondary database serves as an intermediary between the need for statistics updates and the primary database operations. By performing updates on the secondary copy first and only applying them to the primary database when improvements are detected, the intermediary prevents direct resource conflicts while still achieving the goal of maintaining accurate statistical metadata
2Reliability
If statistics update operations are performed frequently on the primary database, then database statistical metadata remains current, but unnecessary resource conflicts and inefficiencies occur when updates do not improve query efficiency
Solution Approach 1:
The patent implements a feedback mechanism that detects whether statistics updates actually improve query efficiency before applying them to the primary database. This feedback loop prevents unnecessary updates from being performed, reducing computational resource consumption while still maintaining current statistical information when improvements are detected
Solution Approach 2:
Instead of performing complete statistics updates on the primary database every time, the system performs partial updates only when query efficiency improvements are detected through testing on the secondary database. This selective approach reduces unnecessary resource consumption while maintaining statistical information currency
Data Source
AI summary
Computer technology for use in a computer system including a database system including a database management sub-system, a primary database and a secondary database, with the secondary database being maintained at least approximately in synchronization with the primary database over time as changes are made to data of the primary database (for example, a database system that includes disaster recovery (DR) features). A statistics update is performed first on a standby database to determine whether query efficiency and/or query optimization really will be substantially improved by performance of the statistics update. If not, then statistics update on the primary database is postponed until a future time.