Distinct Value Estimation for Database Query Execution Plans

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Database management systems (DBMSs) face challenges in efficiently processing queries with join operations by generating optimal query execution plans, as existing methods do not effectively estimate the execution costs based on distinct values in attributes, leading to suboptimal performance.

Innovation Solution

The system generates candidate query execution plans with reduction operations and estimates execution costs by determining the number of distinct values in attributes, allowing for the selection of the most cost-effective plan to execute queries efficiently.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If traditional query execution plan generation methods are used, then the system can process queries, but the execution cost estimation is inaccurate leading to suboptimal performance

Engineering Contradiction:

Improveexecution cost estimation accuracyVSAvoidquery processing performance

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The system performs preliminary sampling of data from tables before generating query execution plans. By selecting a subset of rows and calculating distinct values in advance, the system obtains accurate cost estimation data without processing the entire dataset, thus improving measurement precision while maintaining productivity

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system uses small sample subsets of data temporarily for cost estimation purposes. These samples are inexpensive to create and discard after use, allowing accurate execution cost calculation without the overhead of processing complete tables, thereby resolving the contradiction between precision and productivity

Inventive Principle:

Principle #27Cheap short-living objects (Disposable)

2Measurement precision

If the system processes all data to accurately determine distinct values, then measurement precision improves, but processing time and computational resources increase

Engineering Contradiction:

Improvedistinct value count accuracyVSAvoidquery processing time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The system performs partial processing by sampling a subset of rows rather than processing all data. This partial action provides sufficiently accurate distinct value counts for cost estimation while dramatically reducing processing time, effectively resolving the contradiction between precision and time consumption

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The system changes the parameter of data volume from complete tables to sampled subsets. By adjusting this parameter, the system achieves an optimal balance where distinct value estimation remains accurate enough for practical purposes while processing time is significantly reduced

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS11397733B2Estimating distinct values for database systems

Publication Date: 2022.07.26 SAP SE

AI summary

Some embodiments provide a non-transitory machine-readable medium stores a program. The program receives a query for data that includes a join operation. The program further generates a plurality of candidate query execution plans based on the query, each candidate query execution plan comprising a set of reduction operations. The program also determines a plurality of execution costs associated with the plurality of sets of reduction operations in the plurality of candidate query execution plans. The program further selects a query execution plan from the plurality of candidate query execution plans based on the plurality of execution costs. The program also executes the query execution plan to generate a set of query results for the query.