Query Optimizer Precision Loss Syntax

Solution Overview

Problem

Database query optimization often results in reduced precision of query results due to restrictions on applying certain transformations like rounding and type conversions, which limits the optimizer's ability to improve query performance and memory efficiency.

Innovation Solution

Introducing a syntax extension, ALLOW_PRECISION_LOSS(), that allows the optimizer to apply query rewritings such as rounding and aggregation reordering, even if they result in reduced precision, to enhance query performance and memory efficiency by enabling the optimizer to reorder expressions and apply additional rewritings in query execution plans.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If query optimization is applied to improve query performance and memory efficiency, then query processing speed and memory consumption are improved, but precision of query results is reduced

Engineering Contradiction:

Improvequery processing performanceVSAvoidquery result precision

Core Design Contradiction:

ProductivityVSMeasurement precision

Solution Approach 1:

The system dynamically adjusts the optimization level based on the ALLOW_PRECISION_LOSS syntax. When the syntax is present, the optimizer applies aggressive transformations including rounding and aggregation reordering. When absent, the optimizer maintains strict precision. This dynamic behavior allows the system to adapt its optimization strategy to user requirements, resolving the contradiction between performance and precision.

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The invention changes the precision parameter of query results based on the presence of the ALLOW_PRECISION_LOSS syntax. By detecting this syntax, the system modifies its optimization parameters to allow transformations that would otherwise be restricted, thereby improving performance while controlling precision loss according to user intent.

Inventive Principle:

Principle #35Parameter changes

2Measurement precision

If query optimization transformations like rounding and type conversions are restricted to maintain precision, then query result accuracy is maintained, but query performance and memory efficiency are limited

Engineering Contradiction:

Improvequery result accuracyVSAvoidquery processing performance

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The system dynamically adjusts the optimization level based on the ALLOW_PRECISION_LOSS syntax. When the syntax is present, the optimizer applies aggressive transformations including rounding and aggregation reordering. When absent, the optimizer maintains strict precision. This dynamic behavior allows the system to adapt its optimization strategy to user requirements, resolving the contradiction between performance and precision.

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The invention changes the precision parameter of query results based on the presence of the ALLOW_PRECISION_LOSS syntax. By detecting this syntax, the system modifies its optimization parameters to allow transformations that would otherwise be restricted, thereby improving performance while controlling precision loss according to user intent.

Inventive Principle:

Principle #35Parameter changes

3Speed

If aggressive query optimization is applied to maximize performance improvement, then query processing speed is improved, but memory consumption increases due to additional computation

Engineering Contradiction:

Improvequery processing speedVSAvoidmemory consumption

Core Design Contradiction:

SpeedVSLoss of energy

Solution Approach 1:

The invention changes the precision parameter of query results based on the presence of the ALLOW_PRECISION_LOSS syntax. By detecting this syntax, the system modifies its optimization parameters to allow transformations that would otherwise be restricted, thereby improving performance while controlling precision loss according to user intent.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS11360977B2Selectively allowing query optimization in query processing

Publication Date: 2022.06.14 SAP SE

AI summary

Implementations of the present disclosure include receiving, by a query processing server, a first query, the first query including a syntax indicating that precision loss is allowed, processing, by the query processing server, the first query to provide a first QEP, optimization of which would result in precision loss in a generated query result, determining that the syntax is provided, and in response, optimizing the first QEP to provide an optimized QEP, executing the optimized QEP to generate a query result that includes data stored within a database system, the query result having reduced accuracy relative to a query result that would be generated based on the first QEP, and providing the query result as output from the database system.