Materialized View Refresh for Storage Reporting
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current storage management systems face inefficiencies due to separate schema optimizations for database updates and querying, leading to performance issues in generating reports, as databases optimized for updates are not conducive to efficient querying.
Innovation Solution
Implementing a data warehouse using materialized views within the production database, which are refreshed in an optimized and controlled manner to balance data freshness and reduce query overhead, allowing for efficient reporting while minimizing disk resource usage and contention.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a database schema is optimized for update transactions (insertions), then update performance is improved, but querying performance deteriorates
Solution Approach 1:
The patent segments the database schema into two distinct parts: a production database schema optimized for update transactions and a data warehouse schema optimized for querying. This segmentation allows each schema to be independently optimized for its specific purpose without compromising the other, resolving the contradiction between update performance and querying speed
2Productivity
If a data warehouse is built on a separate system, then reporting performance is improved, but system complexity increases
Solution Approach 1:
The patent merges the data warehouse functionality directly into the production database by using materialized views, rather than building a completely separate data warehouse system. This integration approach improves reporting performance while minimizing the increase in system complexity by utilizing existing database infrastructure
3Loss of information
If materialized views are refreshed frequently, then data freshness is improved, but disk resource usage increases
Solution Approach 1:
The patent implements periodic refreshing of materialized views at controlled intervals rather than continuously or on every data change. This periodic action maintains acceptable data freshness for reporting purposes while significantly reducing disk resource usage and refresh overhead compared to continuous synchronization
Data Source
AI summary
A method of refreshing a materialized view includes creating a materialized view based on a first result of a database query executed against a set of tables stored in a database and discovering a set of child storage objects associated with a parent storage object. The method also includes inserting, based on discovering the set of child storage objects, an entry into one or more tables of the set of tables, the entry including a child storage object of the set of child storage objects. The method further includes delaying a refresh of the materialized view until after the entry is inserted into the set of tables and then refreshing the materialized view, which is based on a second result of the database query executed against the set of tables.