Count Distinct Vertical Union Data Flow Graph

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Advanced databases face inefficiencies in calculating 'count distinct' operations due to resource-intensive join operations, which consume significant time and processing resources.

Innovation Solution

A data flow graph is generated with nodes for aggregation operations and vertical union of columns, allowing for efficient execution of 'count distinct' queries by sorting results on join attributes and appending columns using a vertical union operation, particularly in an in-memory columnar database.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If join operation is used to calculate count distinct, then intermediate results can be combined, but processing resources and time are unnecessarily consumed

Engineering Contradiction:

Improvecorrectness of count distinct calculationVSAvoidquery execution speed

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The patent extracts the join operation from the count distinct calculation process. Instead of using join to combine intermediate results, the system uses vertical union operations to append columns directly, eliminating the need for complex join logic while maintaining calculation correctness

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The patent changes the operational parameters from traditional row-oriented join operations to column-oriented vertical union operations. This parameter change leverages the columnar storage structure to perform count distinct calculations more efficiently by appending columns vertically rather than joining rows horizontally

Inventive Principle:

Principle #35Parameter changes

2Reliability

If join operation is used to calculate count distinct, then intermediate results can be combined, but memory consumption increases

Engineering Contradiction:

Improvecorrectness of count distinct calculationVSAvoidmemory consumption

Core Design Contradiction:

ReliabilityVSQuantity of substance

Solution Approach 1:

The patent extracts the memory-intensive join operation from the calculation process and replaces it with a lighter vertical union operation that appends columns directly, significantly reducing memory consumption while preserving the ability to combine intermediate results correctly

Inventive Principle:

Principle #2Taking out (Extraction)

3Ease of manufacture

If conventional aggregation operations are used, then calculations can be performed, but complex joins are required which consume significant resources

Engineering Contradiction:

Improveease of query executionVSAvoidcomplexity of join operations

Core Design Contradiction:

Ease of manufactureVSDevice complexity

Solution Approach 1:

The patent extracts the complex join operations from the aggregation process and replaces them with simple vertical union operations. This extraction simplifies the overall query execution plan while maintaining the ability to perform accurate count distinct calculations across multiple aggregation levels

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

Instead of the conventional approach of joining results horizontally, the patent inverts the operation by appending columns vertically. This inversion fundamentally changes the complexity profile from complex joins to simple column concatenation

Inventive Principle:

Principle #13The other way round (Inversion)

Data Source

PatentUS9305065B2Calculating count distinct using vertical unions

Publication Date: 2016.04.05 SAP SE

AI summary

A query statement is received that specifies a count distinct. Thereafter, a data flow graph that comprises a plurality of nodes for executing the query is generated. The nodes provide aggregation operations, sorting of results on join attributes and vertically appending columns of count distinct results with intermediate results from at least one of the aggregation operations. Thereafter, execution of the query is initiated using the data flow graph. Related apparatus, systems, techniques and articles are also described.