Autonomous Analytical Function Property Learning for Database Optimization

Solution Overview

Problem

Database query optimizers struggle to generate optimal execution plans for queries involving analytical functions due to unknown properties, requiring manual creation of function descriptors, which is time-consuming and non-scalable.

Innovation Solution

An autonomous learning system learns analytical function properties from observed behavior and generates recommendations for function descriptors, reducing the burden on domain experts and enabling the database management system to discover properties more efficiently.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If manual creation of function descriptors is used, then query optimization can be achieved, but the process is time-consuming and non-scalable

Engineering Contradiction:

Improvequery optimization qualityVSAvoidtime to create function descriptors

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The system enables analytical functions to self-describe their properties through metadata annotations that developers add to function definitions. This self-service approach eliminates the need for manual property extraction while maintaining accurate query optimization information.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

Function descriptors and their properties are extracted and prepared in advance during the function definition phase, rather than being created manually when needed for query optimization. This preliminary action stores optimization information ready for immediate use.

Inventive Principle:

Principle #10Preliminary action

2Measurement precision

If manual creation of function descriptors is used, then query optimization can be achieved, but the process is labor-intensive and lacks scalability

Engineering Contradiction:

Improvequery optimization qualityVSAvoidscalability of function descriptor creation

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The system enables analytical functions to self-describe their properties through metadata annotations that developers add to function definitions. This self-service approach eliminates the need for manual property extraction while maintaining accurate query optimization information.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

Function descriptors are automatically copied and stored in the database catalog from the function definition metadata, eliminating repetitive manual creation work and enabling scalable deployment across multiple functions.

Inventive Principle:

Principle #26Copying

3Device complexity

If analytical function properties are unknown to the DBMS, then the system remains simple, but optimal execution plans cannot be generated

Engineering Contradiction:

ImproveDBMS system simplicityVSAvoidexecution plan quality

Core Design Contradiction:

Device complexityVSMeasurement precision

Solution Approach 1:

The system enables analytical functions to self-describe their properties through metadata annotations that developers add to function definitions. This self-service approach eliminates the need for manual property extraction while maintaining accurate query optimization information.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system changes the state of function properties from unknown to known by extracting and storing descriptive metadata during function definition, enabling the optimizer to generate optimal execution plans without increasing operational complexity.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS11409743B2Property learning for analytical functions

Publication Date: 2022.08.09 TERADATA US INC

AI summary

In some examples, a system learns properties of an analytical function based on information of queries invoking the analytical function that have been previously executed, creates a function descriptor for the analytical function based on the learning, and provides the function descriptor for use by an optimizer in generating an execution plan for a received database query that includes the analytical function.