Remote Nested Join Between AMPs for Column Partitioned Tables

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

The availability and usage of remote nested joins in Relational Database Management Systems (RDBMSs) are limited due to restrictive conditions, which hinders efficient query processing and resource utilization, especially for Column Partitioned (CP) tables with Primary Amp (PA) indexes.

Innovation Solution

A method is introduced to identify and support remote nested join conditions between tables in RDBMSs, allowing query optimizers to generate plans for CP PA tables, enabling remote access through unique and primary indexes, and optimizing resource utilization by reducing the number of AMPs involved in join operations.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If remote nested join is used between AMPs, then query execution efficiency is improved and resource utilization is optimized, but availability and usage are limited due to restrictive conditions

Engineering Contradiction:

Improvequery execution efficiencyVSAvoidavailability of remote nested join

Core Design Contradiction:

ProductivityVSAdaptability or versatility

Solution Approach 1:

The patent changes the parameters and conditions under which remote nested joins can be performed. It extends availability to Column Partitioned (CP) tables with Primary Amp (PA) indexes by modifying the index access mechanisms and enabling remote AMP identification through hash functions applied to join conditions, thereby increasing adaptability while maintaining efficiency

Inventive Principle:

Principle #35Parameter changes

2Adaptability or versatility

If table redistribution or duplication is performed for join operations, then join availability is improved, but resource consumption and processing overhead increase

Engineering Contradiction:

Improvejoin operation availabilityVSAvoidresource consumption

Core Design Contradiction:

Adaptability or versatilityVSLoss of energy

Solution Approach 1:

The patent extracts and eliminates the need for table redistribution or duplication by implementing a message-based remote access mechanism. Instead of moving or copying tables between AMPs, the system sends targeted messages to specific remote AMPs to retrieve only the necessary matching rows, thereby maintaining join availability while significantly reducing resource consumption

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

Rather than copying entire tables for join operations, the patent implements a selective copying mechanism where only specific matching rows are retrieved from remote AMPs through message passing. This partial copying approach maintains the ability to perform joins while minimizing the resource overhead associated with data duplication

Inventive Principle:

Principle #26Copying

3Power

If more AMPs are involved in join processing, then processing power is increased, but system complexity and coordination overhead increase

Engineering Contradiction:

Improveprocessing powerVSAvoidsystem coordination complexity

Core Design Contradiction:

PowerVSDevice complexity

Solution Approach 1:

The patent segments the join processing workload by identifying and involving only the specific AMPs that hold the necessary data. Through hash function-based AMP identification, the system divides the processing task across minimal necessary AMPs rather than involving all AMPs, thereby maintaining processing power while reducing coordination complexity

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent introduces a message-passing intermediary mechanism that simplifies coordination between AMPs. Instead of direct complex coordination, AMPs communicate through standardized messages that contain join conditions and data, acting as an intermediary layer that reduces the complexity of inter-AMP coordination while enabling distributed processing

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS10180961B2Remote nested join between primary access module processors (AMPs)

Publication Date: 2019.01.15 TERADATA US INC

AI summary

A query optimizer identifies a condition that supports a remote nested join. A first Access Module Processor (AMP) is sent the condition to obtain a first set of rows that satisfy the condition from a first table and the first AMP is instructed to perform the remote nested join with a second remote AMP to acquire a second set of rows that satisfy the condition from a second table. The first AMP joins the first set of rows and the second set of rows as a join result, which is returned to the query optimizer.