Predicate Ordering in Column Partitioned Databases

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Inefficient and inaccurate predicate ordering in column partitioned databases can adversely impact query performance, as existing algorithms only consider CPU cost and selectivity, neglecting the importance of column partition access order and grouping predicates by referenced partitions.

Innovation Solution

A two-step predicate ordering algorithm that groups predicates based on referenced column partitions and orders them within each group and across groups to minimize overall cost, using new data structures to analyze and optimize predicate evaluation sequences.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Power

If predicates are ordered using existing algorithms that only consider CPU cost and selectivity, then CPU efficiency is improved, but overall query performance deteriorates due to neglecting column partition access order and IO costs

Engineering Contradiction:

ImproveCPU efficiencyVSAvoidoverall query performance

Core Design Contradiction:

PowerVSProductivity

Solution Approach 1:

The patent extends the predicate ordering criteria by introducing new parameters (IO cost, column partition access patterns) alongside the traditional CPU cost and selectivity metrics. This multi-parameter cost model enables the optimizer to evaluate and order predicates based on their comprehensive impact on query performance, resolving the contradiction between CPU efficiency and overall productivity.

Inventive Principle:

Principle #35Parameter changes

2Adaptability or versatility

If predicates are evaluated without grouping by referenced column partitions, then evaluation flexibility is maintained, but IO cost increases due to redundant column partition accesses

Engineering Contradiction:

Improveevaluation flexibilityVSAvoidIO cost

Core Design Contradiction:

Adaptability or versatilityVSLoss of energy

Solution Approach 1:

The patent groups predicates that reference the same set of column partitions into equivalence classes. By evaluating grouped predicates together after accessing their shared column partitions, the system eliminates redundant IO operations while preserving evaluation flexibility through maintained ordering options within each group.

Inventive Principle:

Principle #5Merging (Combining)

3Ease of operation

If column partitions are accessed for each predicate individually, then predicate evaluation independence is maintained, but the number of logical rows accessed increases

Engineering Contradiction:

Improvepredicate evaluation independenceVSAvoidnumber of logical rows accessed

Core Design Contradiction:

Ease of operationVSQuantity of substance

Solution Approach 1:

The patent performs preliminary grouping of predicates by their referenced column partitions before execution. This pre-processing step identifies opportunities to share column partition accesses across multiple predicates, allowing the system to reduce the total number of logical rows accessed while maintaining evaluation independence through structured grouping.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS11693883B2Techniques for ordering predicates in column partitioned databases for query optimization

Publication Date: 2023.07.04 TERADATA CORP

AI summary

Techniques for ordering predicates in column partitioned databases for query optimization. Predicates on a single CP table within a query are organized to predicate-CP nodes with various sets of column partitions. The predicates within each predicate-CP node and the predicate-CP nodes as a whole are ordered in ascending order of cost which is determined by CPU/IO cost and predicate selectivity. The reorganized query is then executed.