Materialized View Construction Rules for Consistent Database Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Inconsistent data between base tables and materialized views occurs when data is updated, particularly in tables with unique or aggregate models, leading to inaccuracies in query efficiency.
Innovation Solution
A method for constructing materialized views in a database system that determines whether a query statement satisfies preset construction conditions based on the type of the base table, including checks for aggregate operations and sub-statements referencing the base table, ensuring data consistency by constructing the view only when these conditions are met.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If materialized view is constructed for base tables with unique or aggregate models, then query efficiency is improved, but data inconsistency occurs between base tables and materialized views when data is updated
Solution Approach 1:
The patent applies preliminary action by establishing construction conditions before creating materialized views. The system evaluates whether the base table model (unique or aggregate) satisfies predefined construction conditions before allowing materialized view creation. This preventive approach ensures that only suitable base tables are used for materialized views, avoiding future data inconsistency issues while still enabling query efficiency improvements for eligible tables.
2Reliability
If materialized view construction is restricted to avoid data inconsistency, then data accuracy is maintained, but query efficiency improvement is limited
Solution Approach 1:
The patent applies local quality by differentiating construction rules based on the specific type of base table. Instead of universally restricting or allowing materialized view construction, the system evaluates each base table's model characteristics (unique vs. aggregate) and applies appropriate construction conditions. This localized approach ensures data accuracy for tables with unique models while still permitting efficient materialized view construction for aggregate tables that meet the criteria.
3Reliability
If construction conditions are evaluated for all base tables, then data inconsistency probability is reduced, but system complexity increases
Solution Approach 1:
The patent applies parameter changes by transforming the construction condition evaluation into a systematic process based on base table model parameters. The system checks specific parameters such as whether the base table has a unique model or aggregate model, and whether it includes monotonically increasing columns. By parameterizing the evaluation criteria, the system reduces complexity compared to general-purpose evaluation while maintaining high data consistency through targeted condition checking.
Data Source
AI summary
A materialized view construction method, a device, and a medium in a database system are provided. The method includes: receiving a materialized view construction request for a target base table; determining whether the materialized view construction request satisfies a preset construction condition after determining that the target base table belongs to a target type; and constructing a materialized view of the target base table based on a target query statement when determining that the materialized view construction request is executable.