Parallel Group-By Query Processing with Hash Table Redistribution

Solution Overview

Problem

In parallel processing systems, queries with maximum or minimum equality conditions require multiple scans of large tables, leading to extensive resource consumption and inefficiency.

Innovation Solution

A system and method that utilize a hash table to process queries with equality conditions by scanning the table once, redistributing rows based on hash values, and performing global aggregation to compute results without rescanning, optimizing the execution of group-by queries.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If the system scans the table twice to process maximum or minimum equality conditions, then the query results are accurate, but the resource consumption and processing time become extensive

Engineering Contradiction:

Improvequery result accuracyVSAvoidprocessing time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The patent applies preliminary action by computing and storing the maximum or minimum attribute values during the first table scan, then using these pre-computed values to guide the second scan. This allows the system to avoid re-computing aggregate values and directly filter rows matching the extreme values, resolving the contradiction between accurate query results and processing time by preparing necessary information in advance

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent uses copying by creating and broadcasting a copy of the computed maximum or minimum attribute values to all processing modules. This copied information is then used locally at each module to filter rows without requiring continuous communication or re-scanning, thereby maintaining query accuracy while reducing overall processing time and resource consumption

Inventive Principle:

Principle #26Copying

2Reliability

If the system scans the table twice to process maximum or minimum equality conditions, then the query results are complete, but the resource consumption becomes extensive

Engineering Contradiction:

Improvequery result completenessVSAvoidresource consumption

Core Design Contradiction:

ReliabilityVSUse of energy by moving object

Solution Approach 1:

The patent applies preliminary action by computing and storing the maximum or minimum attribute values during the first table scan, then using these pre-computed values to guide the second scan. This allows the system to avoid re-computing aggregate values and directly filter rows matching the extreme values, resolving the contradiction between accurate query results and processing time by preparing necessary information in advance

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent uses copying by creating and broadcasting a copy of the computed maximum or minimum attribute values to all processing modules. This copied information is then used locally at each module to filter rows without requiring continuous communication or re-scanning, thereby maintaining query accuracy while reducing overall processing time and resource consumption

Inventive Principle:

Principle #26Copying

3Measurement precision

If the system uses multiple scans to process equality conditions on large tables, then the query accuracy is maintained, but the productivity decreases

Engineering Contradiction:

Improvequery accuracyVSAvoidquery processing efficiency

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The patent applies preliminary action by computing and storing the maximum or minimum attribute values during the first table scan, then using these pre-computed values to guide the second scan. This allows the system to avoid re-computing aggregate values and directly filter rows matching the extreme values, resolving the contradiction between accurate query results and processing time by preparing necessary information in advance

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent uses copying by creating and broadcasting a copy of the computed maximum or minimum attribute values to all processing modules. This copied information is then used locally at each module to filter rows without requiring continuous communication or re-scanning, thereby maintaining query accuracy while reducing overall processing time and resource consumption

Inventive Principle:

Principle #26Copying

Data Source

PatentUS10459912B2Optimizing processing of group-by queries featuring maximum or minimum equality conditions in a parallel processing system

Publication Date: 2019.10.29 TERADATA US INC

AI summary

A system, method, and computer-readable medium for optimized processing of queries that feature maximum or minimum equality conditions are provided. The disclosed mechanisms provide for a single-scan of the table on which the group-by query is applied. When the table is scanned, each processing module dynamically keeps track of the row(s) having a value of the attribute on which the equality condition is applied that equals or exceeds the maximum attribute value (assuming a maximum equality condition is applied) previously encountered by the processing module. Subsequently, a global aggregation process is then performed to compute the query's result without rescanning the table. Queries featuring a minimum equality condition are similarly processed in accordance with the disclosed embodiments.