Dynamic Partitioning via Tracking Table for SQL Analytic Functions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database management systems face limitations in flexibility due to static partitions defined by the PARTITION BY clause in SQL, which restricts the ability to perform ordered analytic functions dynamically.
Innovation Solution
The introduction of a dynamic partitioning mechanism in database queries, where a clause specifies a partitioning condition that evaluates to true to create dynamic partitions, allowing for dynamic partitioning during query processing, using derived tables to track rows and identify new partitions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If static partitions are used in SQL queries, then the database system maintains simplicity and reliability, but the flexibility and adaptability for dynamic analytic functions are limited
Solution Approach 1:
The patent transforms static partitions into dynamic partitions by introducing a partitioning condition that is evaluated at runtime. Instead of fixing partitions beforehand, the system dynamically determines partition membership by evaluating a boolean condition on each row, allowing partitions to adapt based on data characteristics and query requirements.
Solution Approach 2:
The patent introduces an intermediary derived table that contains a tracking column indicating whether the partitioning condition evaluates to true for each row. This intermediary structure mediates between the input table and the final query results, enabling dynamic partition identification without fundamentally altering the core SQL execution engine.
2Adaptability or versatility
If dynamic partitioning is implemented using derived tables with tracking columns, then partitioning flexibility is improved, but the computational overhead and processing time increase
Solution Approach 1:
The patent performs preliminary evaluation of the partitioning condition and creates the derived table with tracking columns before the main query execution. By pre-computing which rows belong to which partitions and storing this information in the derived table, the system avoids repeated condition evaluations during query processing, thereby reducing overall processing time despite the additional initial computation.
3Measurement precision
If multiple derived tables are created to track partitioning conditions, then dynamic partition identification accuracy is improved, but the device complexity and resource consumption increase
Solution Approach 1:
The patent extracts only the essential information needed for dynamic partitioning into a separate derived table with a specific tracking column. Instead of creating complex multi-table structures, the solution extracts the partitioning condition evaluation results into a dedicated column in a derived table, maintaining accuracy while minimizing structural complexity.
Data Source
AI summary
A query to perform an ordered analytic function contains a clause specifying creation of dynamic partitions, where the clause contains a partitioning condition to specify the dynamic partitions. During processing of the query, dynamic partitions are identified based on evaluating the partitioning condition. To identify the dynamic partitions, a first table is created that contains a first column to track rows of an input table for indicating whether the partitioning condition evaluates to true. The first column of the first table is used to identify the dynamic partitions.