Database Key Range Query Optimization

Solution Overview

Problem

The existing systems face inefficiencies and resource exhaustion due to 'key range explosions' when deriving key ranges for database queries involving multiple primary key columns, leading to excessive memory allocation and long computation times.

Innovation Solution

A database node converts conditional expressions into AND/OR expressions, performs operations on key spaces, and detects potential key range explosions by dropping primary key columns to prevent resource overload, thereby generating key ranges efficiently.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If key ranges are derived for database queries involving multiple primary key columns, then query optimization is improved, but key range explosions occur leading to excessive memory allocation and resource exhaustion

Engineering Contradiction:

Improvequery optimization efficiencyVSAvoidmemory allocation

Core Design Contradiction:

ProductivityVSQuantity of substance

Solution Approach 1:

The patent segments the key space into multiple key ranges by dividing the primary key column values into distinct intervals. This segmentation allows the system to process and manage key ranges in smaller, more manageable units rather than handling the entire key space at once, thereby preventing memory exhaustion while maintaining query optimization effectiveness.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent implements a threshold-based mechanism that performs partial key range derivation. When the number of derived key ranges approaches a predefined threshold, the system stops further derivation and switches to an alternative execution strategy. This partial action prevents key range explosions and excessive memory allocation while still providing optimization benefits for queries where key range derivation is beneficial.

Inventive Principle:

Principle #16Partial or excessive action

2Productivity

If key ranges are derived for database queries involving multiple primary key columns, then query optimization is improved, but computation time increases due to key range explosions

Engineering Contradiction:

Improvequery optimization efficiencyVSAvoidcomputation time

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The patent implements a threshold-based mechanism that performs partial key range derivation. When the number of derived key ranges approaches a predefined threshold, the system stops further derivation and switches to an alternative execution strategy. This partial action prevents key range explosions and excessive memory allocation while still providing optimization benefits for queries where key range derivation is beneficial.

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The patent establishes a threshold for the maximum number of key ranges to derive beforehand, serving as a protective measure against key range explosions. This pre-established threshold cushions the system against excessive computation time by preventing the derivation process from escalating into resource-intensive operations, allowing the system to switch strategies before time is wasted on unproductive computation.

Inventive Principle:

Principle #11Beforehand cushioning (Prior cushioning)

3Measurement precision

If all primary key columns are used to construct key spaces, then key range precision is improved, but key range explosions occur

Engineering Contradiction:

Improvekey range precisionVSAvoidnumber of key ranges

Core Design Contradiction:

Measurement precisionVSQuantity of substance

Solution Approach 1:

The patent segments the key space into multiple key ranges by dividing the primary key column values into distinct intervals. This segmentation allows the system to process and manage key ranges in smaller, more manageable units rather than handling the entire key space at once, thereby preventing memory exhaustion while maintaining query optimization effectiveness.

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS12072881B2Key range query optimization

Publication Date: 2024.08.27 SALESFORCE INC

AI summary

A computer system receives a database query to be executed against a database having a database table that stores records associated with keys. The database query specifies a set of conditional expressions that affect which ones of those records are returned. Based on the set of conditional expressions, the computer system derives a set of key ranges. The deriving can include representing the set of conditional expressions as a set of key spaces, performing a set of logical operations on pairs of those key spaces to reduce a number of key spaces in the set of key spaces, and converting the set of key spaces into the set of key ranges after performing the set of logical operations.