Pushing Aggregates into Union All Views
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face performance bottlenecks due to excessive spooling during query processing of aggregate operations on UNION ALL views, especially in large datasets distributed across multiple network nodes, which hinders efficient data analysis and management.
Innovation Solution
A method is introduced to push join operations into UNION ALL views, rewriting queries to execute aggregates within the views, thereby optimizing query execution plans and reducing spooling, with cost-based selection between original and rewritten plans to ensure performance improvements.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If aggregate operations are performed on UNION ALL views by first spooling the view, then the query processing is straightforward and simple, but significant performance bottlenecks occur due to excessive spooling of voluminous data distributed over network nodes
Solution Approach 1:
The patent applies preliminary action by pushing aggregate operations down into the UNION ALL view definition before the actual query execution. Instead of spooling complete data sets and then performing aggregates, the system pre-computes aggregate results within each branch of the UNION ALL view, thereby eliminating the need to spool voluminous intermediate data and significantly reducing spooling time while improving query processing efficiency
Solution Approach 2:
The patent segments the aggregate operation into multiple independent aggregate operations executed on each branch of the UNION ALL view separately. Rather than treating the entire UNION ALL view as a single unit requiring complete spooling, the system divides the aggregation task across individual view branches, allowing parallel processing and reducing the amount of data that needs to be spooled at any one time
2Ease of operation
If spooling is used for aggregate operations on UNION ALL views, then the processing approach is simple and uniform, but performance bottlenecks occur in distributed database systems with data across many network nodes
Solution Approach 1:
The patent applies local quality by making the aggregate processing approach adaptive to the specific characteristics of each UNION ALL view branch and its underlying data sources. Instead of applying a uniform spooling approach to all branches, the system pushes aggregates locally into each branch, allowing each to be processed according to its own data distribution and access patterns, thereby improving overall database system performance while maintaining operational simplicity through automated query rewriting
3Reliability
If complete UNION ALL views are spooled before aggregate operations, then all data is available for processing, but significant memory and storage resources are consumed
Solution Approach 1:
The patent extracts the aggregate operation from the top level of the query and pushes it down into the UNION ALL view definition. This extraction eliminates the need to materialize and spool complete UNION ALL view results, as aggregates are computed directly from the underlying base tables or views, thereby maintaining data availability for accurate processing while dramatically reducing the quantity of spooled data
Data Source
AI summary
A query with a UNION ALL (UA) view is detected by a query optimizer. A query execution plan and cost for the query is obtained. The query is rewritten to push aggregates of the original query into the view. A query execution plan is generated for the rewritten query and a cost for executing the rewritten query is obtained. The lowest cost execution plan is selected for execution by a database engine of a database.