Compound Predicate Query Transformation via Temporary Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query statement processing techniques are inefficient, particularly for compound predicate queries, leading to significant handling time and resource consumption due to the lack of effective transformation and optimization methods.
Innovation Solution
A method is introduced that involves parsing query statements to identify compound predicates, rewriting them by generating temporary tables and specifying join functions, and evaluating candidate access paths to select the most efficient execution path, thereby reducing query handling time and resource consumption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If compound predicate query statements are processed using existing database query processing techniques, then the query can be executed, but the handling time and resource consumption are significant and inefficient
Solution Approach 1:
The compound predicate query statement is segmented into multiple individual predicate queries. Each predicate is extracted and processed separately to generate candidate access paths, which are then evaluated and combined. This segmentation allows the system to handle complex queries by breaking them down into manageable components that can be optimized independently.
Solution Approach 2:
The system performs preliminary parsing and analysis of the compound predicate query statement to identify individual predicates before execution. Candidate access paths are pre-evaluated for each predicate, and the optimal path is selected in advance. This preliminary action reduces the actual execution time by preparing the query structure and access strategies beforehand.
2Productivity
If compound predicate query statements are processed using existing database query processing techniques, then the query can be executed, but resource consumption is significant
Solution Approach 1:
By segmenting the compound predicate query into individual predicates, the system can evaluate candidate access paths for each predicate separately and combine results efficiently. This avoids the exponential resource consumption that would occur with brute-force evaluation of the entire compound query at once, reducing overall resource usage while maintaining execution correctness.
Solution Approach 2:
The system evaluates candidate access paths partially for each predicate rather than exhaustively evaluating all possible combinations of the entire compound query. By taking partial action on each component and combining results, the system achieves sufficient optimization without the excessive resource consumption of complete enumeration.
3Productivity
If compound predicate query statements are transformed by generating temporary tables and join functions, then query execution efficiency is improved, but the device complexity increases
Solution Approach 1:
The query transformation process is segmented into distinct phases: parsing the compound predicate query, extracting individual predicates, generating temporary tables for each predicate, creating join functions to combine results, and evaluating candidate access paths. This segmentation of the transformation process makes the complexity manageable and systematic rather than monolithic.
Solution Approach 2:
Temporary tables serve as intermediary structures between the original compound predicate query and the final execution plan. These temporary tables store intermediate results from individual predicate evaluations and facilitate the join operations needed to combine results. The intermediary temporary tables simplify the overall transformation complexity by providing structured intermediate storage.
Data Source
AI summary
Methods, computer program products, and systems are presented. The method computer program products, and systems can include, for instance: obtaining a query statement; parsing the query statement and determining from the parsing that the query statement is a compound predicate query statement that includes a first predicate and a second predicate; responsively to the parsing, rewriting the obtained query statement to provide a transformed query statement, wherein the rewriting includes (a) specifying generating of a temporary table, wherein the specified generating uses data values of the first predicate and (b) specifying a join function that uses the temporary table and a table referenced in the query statement; evaluating a candidate access path associated to the transformed query statement; selecting the candidate access path as an access path for execution; and executing the transformed query statement according to the selected candidate access path for execution.