Query Analysis Logic for Materialized View Recommendations

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing query processing systems face inefficiencies in generating materialized views, leading to unnecessary creation of separate views for equivalent queries due to syntactical differences, which limits incremental refreshability and resource utilization.

Innovation Solution

A query analysis logic that assigns instance numbers based on the structure of join operations rather than query syntax, ensuring equivalent queries receive the same instance numbers and share materialized views, even if their text does not exactly match.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If materialized views are generated based on exact query text matching, then query-specific materialized views can be created, but equivalent queries with different syntax receive different materialized views unnecessarily

Engineering Contradiction:

Improvematerialized view recommendation accuracyVSAvoidnumber of materialized views created

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

The system changes the parameter used for materialized view generation from exact query text matching to join graph structural equivalence. By transforming queries into their join graph representations and comparing these structures, the system identifies equivalent queries that should share the same materialized view, thereby reducing unnecessary view creation while maintaining recommendation accuracy.

Inventive Principle:

Principle #35Parameter changes

Solution Approach 2:

The system creates a canonical representation (join graph) of each query's table relationships and uses this copy for comparison purposes. This canonical form allows equivalent queries with different syntactic representations to be identified as such, enabling them to share the same materialized view recommendation.

Inventive Principle:

Principle #26Copying

2Productivity

If multiple materialized views are created for equivalent queries, then each query can be served by its specific view, but resource utilization is reduced and incremental refreshability is limited

Engineering Contradiction:

Improvequery execution performanceVSAvoidresource consumption for view maintenance

Core Design Contradiction:

ProductivityVSLoss of energy

Solution Approach 1:

The system merges equivalent queries into a single materialized view recommendation by identifying them through join graph structural equivalence. Instead of creating separate materialized views for each equivalent query, the system combines them and assigns a single view that can serve all queries in the equivalence class, improving resource utilization and enabling incremental refreshability.

Inventive Principle:

Principle #5Merging (Combining)

3Ease of operation

If instance numbers are assigned randomly to table instances, then query rewrite can proceed, but unnecessary materialized views are created for equivalent queries

Engineering Contradiction:

Improvequery rewrite processVSAvoidmaterialized view creation complexity

Core Design Contradiction:

Ease of operationVSDevice complexity

Solution Approach 1:

The system replaces random mechanical instance number assignment with a structured approach based on join graph analysis. By analyzing the structural relationships between tables in join graphs, the system assigns instance numbers in a deterministic manner that preserves semantic equivalence, thereby avoiding unnecessary materialized view creation while maintaining the query rewrite process.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Data Source

PatentUS7840553B2Recommending materialized views for queries with multiple instances of same table

Publication Date: 2010.11.23 ORACLE INT CORP

AI summary

Systems, methods, computer-readable media and other embodiments are provided that relate to processing database queries. In one embodiment, a query analysis logic can be configured to analyze a query to identify multiple instances of the same table. Instance numbering for the multiple table instances can then be performed using the query structure such that equivalent queries will likely get the same instance number assignments to improve materialized view recommendations for queries.