Model View to Relational Tree Conversion for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Multi-view databases generate sub-optimal query plans due to model views being interpreted as black boxes, lacking awareness of internal models and statistical properties, which prevents optimal query optimization and plan generation.
Innovation Solution
Converting model views into relational tree structures to facilitate query plan generation and optimization by utilizing a view converter that performs index, join graph, and attribute/key figure conversions, enabling cardinality estimation and optimal query planning.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If model views are used in multi-view databases, then data modeling flexibility and user-friendly graphical interface capabilities are improved, but query optimization performance deteriorates because the database cannot access internal model information
Solution Approach 1:
The patent introduces an intermediary component (model view converter) that translates model views into relational models. This converter acts as a mediator between the graphical model view interface and the SQL query optimizer, enabling the optimizer to access internal model information while users continue to work with intuitive graphical interfaces. The converter generates equivalent SQL representations of model views, allowing the query optimizer to process them with full access to join graphs, filters, and statistical properties.
2Ease of manufacture
If model views are interpreted as black box operators, then implementation simplicity is improved, but query plan generation quality deteriorates due to lack of internal model awareness
Solution Approach 1:
The patent segments the model view processing into two distinct parts: (1) the black box operator interpretation for execution, and (2) the converted relational model for optimization. The model view converter creates a parallel relational representation that preserves the logical structure (join graphs, filters, aggregations) without requiring changes to the execution engine. This segmentation allows the system to maintain implementation simplicity while improving query plan generation quality through access to internal model information.
3Device complexity
If statistical properties of model views are not available, then system complexity is reduced, but query optimization accuracy deteriorates
Solution Approach 1:
The patent applies preliminary action by pre-converting model views into relational models with embedded statistical properties before query optimization occurs. The model view converter extracts and incorporates statistical information (selectivity estimates, row counts, distinct value counts) from the graphical model structure in advance. This allows the query optimizer to access accurate statistical properties without requiring real-time computation or increasing system complexity during query execution.
Data Source
AI summary
Disclosed herein are system, method, and computer tangible medium embodiments for generating a relational database model from a model view that provides an estimate for generating query plan for a query on the model view, according to an embodiment. A join model of a model view set on one or more database tables is received. At least one index conversion of the join model is performed, where the index conversion converts at least one index in the join model into at least one leaf node in a relational model tree. At least one join conversion of the join model is performed where the at least one join conversion generates a node in the relational model tree that joins a first and second leaf nodes in the relational model tree. A requested parameter conversion is performed, where the requested parameter conversion includes an attribute or key figure requested by a query.