Database Query Compilation via Mark Joins
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database query execution methods, particularly those using mutual recursion, are inefficient in handling subqueries, leading to predetermined execution plans with O(n2) runtime, which limits optimization options.
Innovation Solution
The introduction of 'single joins' and 'mark joins' in the relational algebra allows for the translation of subquery constructs into an extended relational algebra, breaking mutual recursion and enabling re-orderable joins, additional optimization options, and an associated unnesting technique.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If mutual recursion approach is used to translate subqueries, then the translation is simple, but the execution plan is predetermined to nested-loop-style with O(n2) runtime
Solution Approach 1:
The patent introduces an intermediary translation layer that converts subqueries into mark join operators before execution. This intermediary step transforms the simple but inefficient mutual recursion approach into a form that enables optimized join operations, allowing the system to maintain translation simplicity while achieving better execution performance through the mark join intermediary
2Stability of the object's composition
If conventional join operators are used, then the relational algebra is standard, but additional optimization options are limited
Solution Approach 1:
The patent segments the join operation into two distinct phases: a mark join phase that identifies matching tuples and creates mark columns, followed by a selection phase that filters results. This segmentation of the join operation into separate functional stages provides additional optimization opportunities while maintaining compatibility with standard relational algebra concepts
3Ease of operation
If subqueries are translated using conventional methods, then the execution plan is straightforward, but join reordering options are restricted
Solution Approach 1:
The patent applies preliminary action by performing mark joins before final result selection. The mark join operator pre-processes the data by creating mark columns that indicate tuple compatibility, allowing subsequent operations to efficiently filter and reorder joins without re-evaluating match conditions. This preliminary marking enables flexible join reordering while maintaining execution correctness
Data Source
AI summary
A method executes at a computer system to retrieve data from a database. Upon receiving a database query, the computer system translates the query into an intermediate representation, and estimates a compilation time to compile the intermediate representation into machine executable code. The query execution time to retrieve a result set is also estimated. In accordance with a determination that the query execution time and compilation time satisfy an interpretation criterion, the computer system invokes a byte code interpreter to interpret the intermediate representation and retrieve the result set from the database. In accordance with a determination that the query execution and compilation times satisfy one of a plurality of compilation criteria, the computer system compiles the intermediate representation to form machine code and executes the machine code to retrieve the result set from the database. In some cases, the query intermediate representation is optimized prior to compilation.