Value-Count Index for Dynamic Partition Enhanced Joining
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face inefficiencies in performing queries that join columns from multiple tables, particularly when dealing with large datasets, as existing methods struggle to effectively exclude non-matching values and manage inequality conditions during the joining process.
Innovation Solution
The method employs value-count indexes to identify qualifying sets by excluding non-matching join-column values, creating worktables, and performing queries on these sets, while also partitioning tables for inequality conditions to optimize the joining process in massively parallel processing systems.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional join methods are used on large datasets, then the database system can process queries, but the efficiency deteriorates due to inability to effectively exclude non-matching values
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing value-count indexes for join columns before the actual join operation. These indexes contain pre-processed information about value frequencies and matching patterns, enabling the system to quickly identify and exclude non-matching values during query execution without re-scanning the entire dataset.
Solution Approach 2:
The patent extracts only the necessary information for joining by creating value-count indexes that isolate relevant value-frequency data from the full tables. This extraction allows the system to work with a reduced dataset containing only values that satisfy the join condition, thereby reducing the quantity of data that needs to be processed during the actual join operation.
2Productivity
If traditional join methods process all data, then completeness is maintained, but time consumption increases due to inability to focus on qualifying datasets
Solution Approach 1:
The system performs preliminary computation of value-count indexes during data loading or maintenance operations, so that when a join query arrives, the pre-computed indexing information is immediately available for rapid processing. This preliminary preparation eliminates the need to scan and evaluate all data rows during query execution.
Solution Approach 2:
The value-count index acts as an intermediary structure between the full tables and the join operation. It mediates the interaction by providing a condensed representation of value-frequency information that enables the system to quickly filter and match rows without directly processing the complete datasets, thereby reducing query execution time.
3Productivity
If value-count indexes are created for all join columns, then join efficiency improves, but device complexity increases
Solution Approach 1:
The patent applies local quality by creating value-count indexes selectively only for columns that are actually used in join operations, rather than indexing the entire table structure. This localized approach ensures that indexing resources are concentrated on the specific join columns that matter, reducing overall system complexity while maintaining join performance benefits.
Solution Approach 2:
The system changes the parameter representation by transforming full column data into compressed value-count representations. Instead of storing and processing all original values, the system uses aggregated count parameters that capture the essential joining information, thereby simplifying the data structure while improving join efficiency.
4Productivity
If the system processes inequality conditions without partitioning, then completeness is maintained, but processing efficiency deteriorates
Solution Approach 1:
The patent applies segmentation by dividing the data into partitions based on the inequality conditions and value-count index information. This segmentation allows the system to process only the relevant partitions that satisfy the inequality conditions rather than scanning the entire dataset, thereby improving efficiency while maintaining completeness through systematic partition processing.
Data Source
AI summary
Methods, computer programs, and database systems for performing a database query to join two or more tables on a condition are disclosed. Each table includes one or more join columns. Each of the join columns includes zero or more join-column values. The method includes excluding join-column values that do not satisfy the condition, using one or more value-count indexes and performing the database query on non-excluded join-column values.