Hash-Based Single-Pass Statistics Collection for Data Store Query Plans

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing data store systems face inefficiencies in collecting statistics for query planning due to the need for disk sorting, which is resource-intensive, and random sampling can lead to inaccurate estimates, especially in skewed datasets, resulting in non-optimal query plans and performance degradation.

Innovation Solution

A data store system generates hash values for column values using a hash function, selects a subset domain of hash values, determines frequency values, and generates unique identifiers to calculate statistics, allowing for single-pass analysis without disk sorting and improving accuracy.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If disk sorting is used to collect statistics, then measurement precision is improved, but use of energy increases and productivity decreases

Engineering Contradiction:

Improvestatistics accuracyVSAvoidsystem resource usage

Core Design Contradiction:

Measurement precisionVSUse of energy by moving object

Solution Approach 1:

The patent extracts only the necessary information (hash values and frequencies) from the data stream during a single pass, rather than performing complete disk sorting. By hashing column values and counting frequencies in memory, the system obtains accurate statistics without the energy-intensive disk sort operation, directly resolving the contradiction between measurement precision and energy consumption

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The patent applies hash functions to column values in advance during the single-pass scan, transforming the data into a compact frequency distribution representation. This preliminary transformation allows accurate statistics collection without subsequent disk sorting, reducing energy usage while maintaining measurement precision

Inventive Principle:

Principle #10Preliminary action

2Measurement precision

If disk sorting is used to collect statistics, then measurement precision is improved, but productivity decreases

Engineering Contradiction:

Improvestatistics accuracyVSAvoidworkload execution performance

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The patent extracts essential statistical information (frequency counts of hash values) during a single-pass scan, eliminating the need for time-consuming disk sorting operations. This extraction approach maintains measurement precision while significantly improving productivity by reducing the time required for statistics collection and enabling faster query plan generation

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

By performing hash-based frequency counting in advance during a single pass through the data, the patent prepares statistics ready for query optimization without requiring subsequent sorting operations. This preliminary action improves productivity by making statistics available immediately for query plan generation

Inventive Principle:

Principle #10Preliminary action

3Use of energy by moving object

If random sampling is used to collect statistics, then use of energy decreases, but measurement precision worsens

Engineering Contradiction:

Improvesystem resource usageVSAvoidstatistics accuracy

Core Design Contradiction:

Use of energy by moving objectVSMeasurement precision

Solution Approach 1:

The patent introduces hash values as an intermediary between the raw column values and the frequency statistics. By hashing all column values and counting frequencies in a single pass, the system achieves both low energy consumption (avoiding disk sorting) and high measurement precision (capturing all unique values and their frequencies), directly resolving the contradiction that plagues random sampling approaches

Inventive Principle:

Principle #24Intermediary (Mediator)

4Use of energy by moving object

If single-pass analysis without disk sorting is used, then use of energy decreases and productivity improves, but measurement precision worsens

Engineering Contradiction:

Improvesystem resource usageVSAvoidstatistics accuracy

Core Design Contradiction:

Use of energy by moving objectVSMeasurement precision

Solution Approach 1:

The patent replaces the mechanical disk sorting process with a computational hash-based frequency counting approach. By using hash functions to transform column values and count frequencies in memory during a single pass, the system achieves both energy efficiency and measurement precision, eliminating the need for disk sorting while maintaining accurate statistics collection

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Data Source

PatentUS10558633B1Hash-value-based single-pass data store statistics collection

Publication Date: 2020.02.11 TERADATA US INC

AI summary

A data store system includes a processor that may generate a hash value based on a hash function for each column value in a selected column of a data store table and may select a first domain and a second domain of hash values. The processor may determine a frequency value for each hash value within the first domain, generate a unique identifier for each hash value within the second domain, and determine at least one statistic on the selected column based on the frequency values and the unique identifiers. The processor may store the at least one statistic for use in a query plan. A method and computer-readable medium may also be implemented.