Query Plan Shared Subplan for Distributed Row Count
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Implementing TOTAL ROWCOUNT calculation in a relational query execution engine in an efficient, easy, and maintainable way is challenging due to complications arising from multiple results and distributed systems, requiring special coding and extra code for distributed summation.
Innovation Solution
A new query plan is introduced with a shared subplan relation between the operator-at-issue and its input, incorporating an aggregation operator to sum up incoming counts and update the resultant sum as a stored variable, applicable in distributed systems.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If TOTAL ROWCOUNT calculation is implemented in distributed systems using existing algorithms, then the system can process queries, but it requires special coding and extra code for distributed summation, increasing device complexity
Solution Approach 1:
The patent introduces an intermediary aggregation operator that acts as a mediator between distributed data sources and the final TOTAL ROWCOUNT calculation. This operator receives row count information from multiple distributed sources, aggregates them locally, and passes the aggregated result upward in the query plan, eliminating the need for complex distributed summation code while maintaining ease of implementation
Solution Approach 2:
The patent segments the TOTAL ROWCOUNT calculation into distributed aggregation operations performed at intermediate nodes rather than requiring centralized collection of all row counts. By dividing the calculation into local aggregation segments across distributed sources, the system reduces code complexity while maintaining implementation ease
2Productivity
If TOTAL ROWCOUNT calculation is implemented in distributed systems with existing algorithms, then basic functionality is achieved, but performance is optimized poorly due to lack of integration with query plan
Solution Approach 1:
The patent merges the TOTAL ROWCOUNT aggregation operation with the existing query execution plan by introducing an aggregation operator that integrates seamlessly into the query pipeline. This allows row count aggregation to occur in parallel with data processing operations, improving query execution efficiency while minimizing calculation overhead through unified operation execution
3Adaptability or versatility
If clause variable calculations are implemented without a unified approach, then specific operations can be performed, but maintainability deteriorates due to scattered special coding
Solution Approach 1:
The patent creates a universal aggregation operator that handles multiple types of clause variable calculations (TOTAL ROWCOUNT, GROUP BY counts, etc.) through a single unified mechanism. This multi-functional operator maintains calculation flexibility for different query types while improving maintainability by eliminating scattered special-coding requirements across the codebase
Data Source
AI summary
In an example embodiment, a query plan is modified/generated to insert a new shared subplan relation between the operator-at-issue and its input (e.g., the child operator). The shared subplan relation has two references, one of which is connected to the operator-at-issue and one of which is connected to an aggregation operator. A new operator is then inserted as a parent of the aggregation operator. This new operator sums up incoming counts and updates the resultant sum as a stored variable (e.g., as a session variable).