Multi-Join Predicate Transfer Pre-Filtering for Faster Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large scale database systems face inefficiencies in searching for records across multiple tables due to complex relationships, leading to increased processing and memory resource consumption and slower responsiveness.
Innovation Solution
Implement predicate transfer pre-filtering through constructing a join graph and transferring local predicates across multiple tables using Bloom filters to reduce join input sizes, applying filters to tables before performing join operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If predicate transfer pre-filtering is implemented across multiple tables, then query runtime is reduced and filtering efficiency is improved, but device complexity and implementation complexity increase
Solution Approach 1:
The patent segments the filtering process by constructing a join graph that divides tables into vertices and join operations into edges. Local predicates are transferred along specific paths in the graph, allowing filtering to be applied incrementally across multiple tables rather than requiring a monolithic filtering approach. This segmentation reduces query runtime while managing system complexity through structured decomposition.
Solution Approach 2:
The patent applies preliminary filtering actions by transferring local predicates to filter tables before join operations are performed. The join graph is constructed and predicates are propagated in advance, identifying and filtering out rows that will not contribute to join results before the actual joins occur. This preliminary action significantly reduces the data volume processed during joins, improving query runtime without proportionally increasing complexity.
2Quantity of substance
If local predicates are transferred across the join graph to filter multiple tables, then memory consumption and hash table probe costs are minimized, but the complexity of predicate management and transfer increases
Solution Approach 1:
The patent extracts only the necessary filtering information by transferring local predicates selectively across the join graph. Instead of managing all possible predicates for all tables, the system identifies and transfers only those predicates that will actually filter rows contributing to join results. This extraction approach minimizes memory consumption while keeping predicate management complexity manageable through targeted rather than comprehensive predicate handling.
Solution Approach 2:
The join graph serves as an intermediary structure that facilitates predicate transfer between tables. Rather than directly managing complex many-to-many predicate relationships between tables, the graph provides a structured intermediate representation where predicates flow along defined edges. This intermediary approach simplifies predicate management while enabling efficient filtering that reduces memory consumption during join operations.
Data Source
AI summary
A method includes receiving a query of a database including a plurality of tables, constructing a join graph, and constructing a local predicate including a local filter. The method also includes transferring the local predicate across the join graph, applying the local filter and one or more transferred filters to the tables of the join graph, performing join operations on the filtered tables of the join graph, and returning a result of the query based on joining the filtered tables.