Dynamic Partition Selection in Distributed Databases

Solution Overview

Problem

Distributed database systems face inefficiencies in query processing due to the need to scan all partitions of a table, even when only a subset is required to satisfy a query, leading to increased computation time and resource utilization.

Innovation Solution

Implementing dynamic partition selection, where a system generates query plans with partition selector operators that determine and eliminate unnecessary partitions at query runtime, using partition selection functions to identify relevant partitions based on predicate expressions.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If all partitions are scanned to ensure complete query results, then query result completeness is guaranteed, but query processing time and resource utilization increase

Engineering Contradiction:

Improvequery result completenessVSAvoidquery processing time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system performs preliminary analysis of the query predicate expression against partition metadata before actual data scanning. The partition selection function evaluates partition keys and ranges in advance to identify candidate partitions that may contain relevant data, eliminating unnecessary partitions from the scan plan before execution begins.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The partition selection mechanism dynamically adapts to different query types and predicate expressions. The system adjusts which partitions to scan based on runtime query characteristics, using dynamic programming to optimize the selection process and eliminate partitions that cannot satisfy the query conditions.

Inventive Principle:

Principle #15Dynamics

2Reliability

If all partitions are scanned to ensure complete query results, then query result completeness is guaranteed, but resource utilization increases

Engineering Contradiction:

Improvequery result completenessVSAvoidresource utilization

Core Design Contradiction:

ReliabilityVSUse of energy by moving object

Solution Approach 1:

The system performs preliminary analysis of the query predicate expression against partition metadata before actual data scanning. The partition selection function evaluates partition keys and ranges in advance to identify candidate partitions that may contain relevant data, eliminating unnecessary partitions from the scan plan before execution begins.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The partition selection mechanism dynamically adapts to different query types and predicate expressions. The system adjusts which partitions to scan based on runtime query characteristics, using dynamic programming to optimize the selection process and eliminate partitions that cannot satisfy the query conditions.

Inventive Principle:

Principle #15Dynamics

3Productivity

If static partition elimination is used based on query values, then some partitions can be eliminated, but dynamic query conditions cannot be optimized

Engineering Contradiction:

Improvequery processing efficiencyVSAvoidpredicate expression flexibility

Core Design Contradiction:

ProductivityVSAdaptability or versatility

Solution Approach 1:

The partition selection function serves multiple purposes: it evaluates simple equality predicates, range queries, and complex dynamic conditions. The same underlying mechanism handles diverse query types by adapting its evaluation strategy based on the predicate expression provided, making the system universally applicable to various query scenarios.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

The partition selection mechanism dynamically adapts to different query types and predicate expressions. The system adjusts which partitions to scan based on runtime query characteristics, using dynamic programming to optimize the selection process and eliminate partitions that cannot satisfy the query conditions.

Inventive Principle:

Principle #15Dynamics

Data Source

PatentUS11263212B1Dynamic partition selection

Publication Date: 2022.03.01 PIVOTAL SOFTWARE INC

AI summary

Methods, systems, and apparatus, including computer programs encoded on computer storage media, for dynamic partition selection. One of the methods includes receiving a representation of a query plan generated for a query, wherein the query plan includes a dynamic scan operator that represents a first computing node obtaining tuples of one or more partitions of a table from storage and transferring the tuples to a second computing node that executes a parent operator of the dynamic scan operator. A partition selector operator is generated corresponding to the dynamic scan operator. A location in the query plan is determined for the partition selector operator. A modified query plan is generated having the partition selector operator at the determined location.