Query-Based Database Partitioning for Multi-Table Consistency
Solution Overview
Problem
Existing database systems face challenges in efficiently partitioning data across multiple tables based on complex query conditions, leading to inconsistent data management and performance degradation due to large data volumes, especially in OLTP databases, as current partitioning mechanisms are limited to column-level operations and require primary and foreign key relationships.
Innovation Solution
Implement a declarative query-based partitioning scheme that allows conditional partitioning across multiple tables, enabling partition-level operations such as archive and purge by defining a partitioning scheme using a SELECT query with nested subqueries, allowing data to be partitioned and managed consistently across related tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If traditional column-level partitioning mechanisms are used, then partitioning can be implemented on single tables, but data consistency across multiple tables cannot be maintained and complex query conditions cannot be handled
Solution Approach 1:
The patent segments the partitioning mechanism into two distinct components: (1) a query-based partitioning definition that specifies complex conditions across multiple tables, and (2) a cascade propagation mechanism that distributes partitioning operations from a primary table to related tables. This segmentation allows each component to specialize - the query definition handles complexity while the propagation mechanism ensures consistency.
Solution Approach 2:
The patent introduces an intermediary cascade propagation mechanism that acts as a mediator between the primary table and related tables. This intermediary automatically translates partitioning operations on the primary table to corresponding operations on related tables based on the defined query conditions, ensuring data consistency without requiring manual intervention.
2Reliability
If manual partitioning management across multiple tables is implemented, then data consistency can be maintained, but system complexity increases and automation is reduced
Solution Approach 1:
The patent implements a self-service mechanism where the cascade propagation automatically handles the coordination of partitioning operations across multiple tables. Once the query-based partitioning definition is established, the system autonomously propagates partitioning decisions from the primary table to related tables, eliminating the need for manual intervention and reducing operational complexity.
Solution Approach 2:
The patent requires preliminary definition of the partitioning query and cascade relationships before actual partitioning operations are performed. By pre-specifying how partitioning should propagate across tables through the query definition, the system prepares the framework in advance, making subsequent partitioning operations automatic and consistent without requiring complex real-time coordination.
3Quantity of substance
If data volumes in OLTP databases grow large, then historical data can be retained, but database performance degrades due to increased data processing time
Solution Approach 1:
The patent segments large volumes of historical data into distinct partitions based on complex query conditions that span multiple tables. By dividing the data into manageable partitions that can be independently managed, the system maintains fast query performance on active data while preserving historical data for compliance and analysis purposes.
Solution Approach 2:
The patent enables partial partitioning where only the portions of data that meet specific query conditions are partitioned and moved to historical storage, while the remaining data stays in the primary table for fast access. This partial action approach ensures that performance-critical operations remain unaffected while historical data is efficiently managed.
4Productivity
If multiple purpose-built databases are implemented to handle different data types, then specific workload performance is improved, but infrastructure requirements and integration complexity increase
Solution Approach 1:
The patent implements a universal partitioning mechanism that can handle complex query conditions across multiple tables within a single database system. This multi-functional capability eliminates the need for multiple specialized databases by providing a unified platform that can efficiently manage diverse data types and query patterns through its query-based partitioning approach.
Solution Approach 2:
The patent merges the functionality of multiple purpose-built databases into a single database system by enabling cross-table query-based partitioning. This combining approach allows the system to handle workloads that would otherwise require separate databases, reducing infrastructure complexity while maintaining the performance benefits of specialized data management.
Data Source
AI summary
A partitioning scheme for a database has an associated partitioning scheme identifier, a partition-defining query, and an indication of a primary table to be partitioned. A first, primary table is associated with first and second partition types, the second being a query type for which the identifier is passed as a parameter. Each of one or more second tables has an associated partition type, namely, a query type for which the identifier is passed as a parameter. The partition-defining query includes one or more outer queries conditioned on an innermost query, the innermost query defined on the first table, each outer query querying a table other than the first. The first table is partitioned based on the first partition type. The database is updated by making a change to a partition of the first table and to a partition of each second table based on the partition-defining query.



