Database Partition Maintenance with Concurrent Row Filtering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems incur high and unnecessary processing overhead when deleting or purging items, especially in scenarios involving mass deletions, due to the costs associated with data manipulation operations like logging and transaction processing.
Innovation Solution
Combining data manipulation operations with partition maintenance operations using query language predicates within data definition language statements to filter out rows during partitioning, thereby reducing the overall processing costs by performing these operations concurrently.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If separate data manipulation operations (delete/purge) are performed followed by table partitioning operations, then data integrity is maintained, but the total processing cost and time become excessively high
Solution Approach 1:
The patent combines data manipulation operations (delete/purge) with table partitioning operations into a single integrated operation. The database system executes both operations concurrently in one maintenance operation, eliminating the need to perform them sequentially. This merging reduces the total maintenance time while maintaining data integrity through the system's transaction management and logging mechanisms.
Solution Approach 2:
The patent performs filtering of rows to be deleted during the partitioning process itself, rather than requiring a preliminary separate delete operation. The system identifies and filters out rows matching the deletion criteria while simultaneously creating partitions, effectively performing the data manipulation action in advance as part of the partitioning operation.
2Reliability
If data manipulation operations are performed with full logging and transaction processing, then data resiliency and disaster recovery are ensured, but the processing overhead and computing resource costs become unnecessarily high
Solution Approach 1:
The patent merges data manipulation operations with table partitioning operations so that both are executed within a single transaction context. This integration allows the system to apply logging and transaction processing once for the combined operation rather than separately for each operation, reducing the redundant overhead while maintaining data resiliency through the database's transaction management system.
3Shape
If table partitioning operations are performed first followed by row deletions over partitions, then data organization is improved, but the total maintenance cost increases significantly
Solution Approach 1:
The patent inverts the traditional sequence by performing filtering and data manipulation during the partitioning operation itself, rather than performing partitioning first and then deletions. The system filters rows and applies deletion criteria concurrently with creating partitions, achieving both data organization and data manipulation in a single operation that is more efficient than the sequential approach.
Data Source
AI summary
A database engine performs row filtering operations concurrently with performing database object partition maintenance operations. The filtering operations do not incur the high processing costs of database delete or purge operations that would incur per row costs for logging, locking, etc. The partitioning and filtering operations are specified in multiple clauses of a single database language statement. The clauses of the statement are executed concurrently. A method implementation receives a database language statement that comprises a partitioning operation specification as well as a filtering operation specification that includes a logical predicate for determining whether or not to filter-out predicate-matching entries when forming the resulting partitioned target database object. While processing the partitioning operations, the method discards entries taken from the source database object whenever the entry corresponds to the logical predicate. The target database object contains some entries that derive from the source, but does not contain the discarded entries.