Subquery Predicate Generation for Multi-Table Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing techniques for subquery predicate generation in multi-table joins are inefficient, as they often read all rows to generate high/low key ranges, which is costly and not applicable when information is not shared between disparate data sources, leading to suboptimal query optimization across distributed systems.
Innovation Solution
A method for efficient subquery predicate generation that determines minimum and maximum ranges for columns using indexes, merges these ranges to create inclusive ranges, and rewrites queries with derived local subquery predicates to reduce data access and improve filtering efficiency, applicable to both local and remote table joins.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If existing techniques read all rows to generate high/low key ranges for subquery predicates, then complete data coverage is achieved, but processing overhead and time consumption increase significantly
Solution Approach 1:
The patent extracts only the necessary minimum and maximum key values from tables using index lookups, rather than reading all rows. This is achieved by querying the index structure directly to obtain bounding values for predicate generation, significantly reducing I/O overhead while maintaining predicate accuracy.
Solution Approach 2:
The patent performs preliminary index lookups to determine minimum and maximum key ranges before executing the full query. By pre-computing these bounding values and incorporating them into subquery predicates, the system filters data earlier in the execution plan, reducing the amount of data that needs to be processed in subsequent join operations.
2Adaptability or versatility
If predicate generation is performed without sharing information between disparate data sources, then distributed system autonomy is maintained, but query optimization capability deteriorates
Solution Approach 1:
The patent segments the predicate generation process into local operations that can be performed independently at each data source. Each site generates its own subquery predicates using local index information, allowing distributed execution without requiring centralized information sharing, thus maintaining system autonomy while enabling optimization.
Solution Approach 2:
Each distributed data source performs self-service predicate generation by utilizing its own local index structures to determine minimum and maximum key ranges. This eliminates the need for inter-site information exchange while still producing optimized predicates, allowing each site to independently improve query execution efficiency.
3Reliability
If full data sets are read to materialize results for predicate generation, then comprehensive predicate coverage is achieved, but memory and storage requirements increase
Solution Approach 1:
The patent extracts only the essential minimum and maximum key values needed for predicate generation, rather than materializing complete data sets. By querying index structures directly for these bounding values, the system achieves sufficient predicate coverage with minimal data extraction, reducing memory and storage requirements.
Solution Approach 2:
The patent applies partial action by reading only the necessary portion of data (minimum and maximum key values from indexes) rather than the full data set. This partial reading approach provides sufficient information for effective predicate generation without the overhead of complete materialization, achieving an optimal balance between predicate completeness and resource consumption.
Data Source
AI summary
Provided are techniques for efficient subquery predicate generation to reduce processing in a multi-table join. It is determined that a query is to be rewritten, wherein the query includes a predicate for a first column of a first table and a second column of a second table. The query is rewritten with generated subquery predicates for the first table that are to be applied to the second table and with generated subquery predicates for the second table that are to be applied to the first table. Minimum and maximum ranges for columns of the generated subquery predicates at a cursor open are determined. The minimum and maximum ranges are merged to determine inclusive ranges that exist in the first table and the second table. The generated subquery predicates are rewritten as derived local subquery predicates with the inclusive ranges. The query with the derived local subquery predicates is executed.