SQL Implication Decider for NULL-Aware Materialized View Reuse
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional SAT and UNSAT checking algorithms fail to support SQL queries due to the indeterminate nature of NULL values, leading to uncertainty in determining query implications and high computational complexity, which hinders the effective use of materialized views for query optimization.
Innovation Solution
A method and system that rewrite SQL queries into disjunctive and conjunctive normal forms to handle NULL values, allowing for efficient determination of query implications by comparing individual terms, enabling the reuse of filtered views for faster query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the database is normalized to avoid data redundancy, then data consistency is improved, but the complexity of SQL queries increases
Solution Approach 1:
The patent segments complex SQL queries into multiple simplified steps by introducing a query decomposition mechanism that breaks down complex queries into simpler sub-queries, making them easier to process and execute while maintaining data consistency in the normalized database structure
Solution Approach 2:
The patent introduces an intermediary component (query processor or translation layer) that mediates between the user's complex query intent and the normalized database structure, automatically handling the complexity of joins and relationships while presenting simplified results to the user
2Speed
If materialized views are used to improve query performance, then query execution speed is improved, but storage requirements increase
Solution Approach 1:
The patent applies partial materialization by materializing only specific portions of queries that benefit most from pre-computation, rather than materializing entire result sets, thus achieving performance improvement while minimizing storage overhead
Solution Approach 2:
The patent dynamically changes materialization parameters such as refresh frequency, retention period, and selection criteria based on query patterns and storage availability, allowing the system to adapt between performance optimization and storage conservation
3Measurement precision
If complex queries are decomposed into multiple steps, then query accuracy is improved, but the time required for query processing increases
Solution Approach 1:
The patent performs preliminary decomposition of complex queries into executable steps before actual query execution, caching the decomposition plan and intermediate results to avoid repeated decomposition overhead, thus maintaining accuracy while reducing processing time
Solution Approach 2:
The patent maintains continuity by executing decomposed query steps in an optimized sequence with overlapping operations where possible, and by caching intermediate results to avoid redundant computation, ensuring that the multi-step process does not significantly increase total processing time
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A system (100) and method (200) for managing queries (134) including receiving a new query comprising a first plurality of conjoined terms (210), accessing a filtered view of a database from memory, the filtered view being filtered by the previously received query according to a filter represented by a second plurality of conjoined terms (220), at least one of the first plurality of conjoined terms or the second plurality of conjoined terms including at least one NULL value, determining that the filter of the new query implies a filter of the previously received query (230), and based on the determination that the filter of the new query implies the filter of the previously received query, executing the new query using the filtered view of the previously received query (240).