Logical Partial Declustering for Dynamic Partition Selection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current relational database systems face inefficiencies due to static mapping of partitions to data-storage facilities, leading to uneven data distribution and increased execution times for complex queries, as the mapping is often physically defined and does not adapt dynamically.
Innovation Solution
Logical partial declustering techniques allow for dynamic partition selection based on static or dynamic parameters, ensuring even data distribution across multiple partitions without requiring physical definitions, thereby optimizing data organization and query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If static mapping of partitions to data-storage facilities is used, then data organization is simplified, but data distribution becomes uneven and query execution time increases
Solution Approach 1:
The patent implements dynamic partition selection where the mapping between partitions and data-storage facilities is not fixed but changes based on query requirements. The system dynamically determines which subset of partitions to use for each query, allowing optimal data distribution without requiring complex static pre-configuration. This resolves the contradiction by making the system adaptable to different query patterns while maintaining simple data organization.
2Ease of operation
If physical partition definitions are used, then partition selection is explicit, but adaptability to different query patterns is reduced
Solution Approach 1:
The system changes the parameter of partition selection from fixed physical definitions to dynamic logical selections based on query characteristics. Instead of being constrained by pre-defined physical partition mappings, the system can select different subsets of partitions dynamically based on the specific query pattern, data distribution, and performance requirements, thereby achieving both operational clarity and adaptability.
3Reliability
If all partitions are used for data storage, then data availability is maximized, but I/O operations and processing overhead increase
Solution Approach 1:
The patent applies partial action by selecting only the necessary subset of partitions for each query rather than accessing all partitions. The system determines the minimal required partition subset based on query requirements and data distribution patterns, thereby maintaining data availability when needed while reducing unnecessary I/O operations and processing overhead for queries that don't require all partitions.
Data Source
AI summary
A method and system for performing logical partial declustering in a shared-nothing database environment. The solution defines a partial declustering technique that can be used to dynamically adapt the declustering of base tables or intermediate result sets. The partial declustering technique is defined to limit the number of partitions (P) involved in an operation. The technique is defined to limit the operation to a subset of the system partitioning map (SPM). There are two components: the association between the data values and partitions is altered by restricting the data values to P′ partition values where P′ is less than P; and the actual partitions of the SPM selected are then derived based on a seed derived from a dynamic parameter, such as a session number, or a static parameter, such as a table identifier. In the case of intermediate result sets the number of P′ partitions selected is a cost based decision based on the estimated number of rows in the input intermediate result sets. In the case of a base table the number of P′ partitions is selected based on the table DDL. There is no physical partitioning definitions, rather logical partitions are defined based on subsetting the SPM.