Function-Based Query Selective Estimator for Database Predicate Optimization

Solution Overview

Problem

Current database systems face inefficiencies in evaluating and optimizing database operations dependent on functions, as calculating functional predicates for all column values is computationally expensive and often impractical, especially when dealing with large datasets and variable inputs.

Innovation Solution

A function-based query selective estimator transforms functional predicates into predicates dependent on column values, allowing for predictions to be made based on a subset of possible column values, reducing the need for extensive calculations and leveraging existing statistics in database systems.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If functional predicates are evaluated for all column values to ensure accurate query optimization, then prediction accuracy is improved, but computational cost increases significantly

Engineering Contradiction:

Improveprediction accuracyVSAvoidcomputational cost

Core Design Contradiction:

Measurement precisionVSUse of energy by moving object

Solution Approach 1:

The patent segments the evaluation process by dividing all possible column values into a representative subset. Instead of evaluating functional predicates for every column value, the system selects a manageable subset of values that capture the essential distribution characteristics, thereby reducing computational cost while maintaining sufficient prediction accuracy for query optimization.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent applies partial action by evaluating functional predicates for only a portion of column values rather than all values. By selecting a representative subset of column values (e.g., based on data distribution, histograms, or sampling), the system achieves adequate prediction accuracy without the excessive computational burden of complete evaluation.

Inventive Principle:

Principle #16Partial or excessive action

2Measurement precision

If functional predicates are evaluated for all column values to improve query optimization, then prediction accuracy is improved, but processing time increases

Engineering Contradiction:

Improveprediction accuracyVSAvoidprocessing time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The patent segments the evaluation process by dividing all possible column values into a representative subset. Instead of evaluating functional predicates for every column value, the system selects a manageable subset of values that capture the essential distribution characteristics, thereby reducing computational cost while maintaining sufficient prediction accuracy for query optimization.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent applies partial action by evaluating functional predicates for only a portion of column values rather than all values. By selecting a representative subset of column values (e.g., based on data distribution, histograms, or sampling), the system achieves adequate prediction accuracy without the excessive computational burden of complete evaluation.

Inventive Principle:

Principle #16Partial or excessive action

3Productivity

If functional predicates are transformed into column-value-dependent predicates to reduce computational cost, then processing efficiency is improved, but prediction accuracy may deteriorate

Engineering Contradiction:

Improveprocessing efficiencyVSAvoidprediction accuracy

Core Design Contradiction:

ProductivityVSMeasurement precision

Solution Approach 1:

The patent applies preliminary action by pre-selecting a representative subset of column values that capture the essential data distribution characteristics before the actual query execution. This preliminary selection process enables the transformation of functional predicates into column-value-dependent predicates on a manageable subset, maintaining prediction accuracy while achieving significant processing efficiency improvements.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent changes the parameter of evaluation from all column values to a representative subset of column values. By adjusting this parameter (the scope of values to evaluate), the system transforms the computational problem into a more efficient form while preserving the essential characteristics needed for accurate prediction.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS9406027B2Making predictions regarding evaluation of functions for a database environment

Publication Date: 2016.08.02 TERADATA CORP

AI summary

A prediction regarding one or more functions can be made for a database environment. In particular, a predication can be made with respect to values stored in at least one column of at least one table in a database, based on the evaluation of one or more functions for a subset of possible column values (i.e., resultant values derived from the evaluation of a subset of possible column values) without the need to calculate the function(s) for all of the actual entries in the column of the table(s). In effect, a functional predicate can be transformed (or translated) to a predicate that is dependent on the column values instead of the evaluation of one or more functions for the column values.