Database Row Deletion via Dedicated Mapping Table
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems designed for analytical workloads lack efficient access paths for individual data rows due to the absence of indexes, leading to high computational costs for deletions, especially in real-time analytics scenarios where frequent updates and deletions are required.
Innovation Solution
A method is introduced where a row is logically deleted by inserting its key into a dedicated table, allowing subsequent queries to identify and delete the corresponding rows from both the main table and the dedicated table during query processing, thereby reducing the computational cost of deletions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If a table scan is used to locate rows for deletion in an analytical database without indexes, then the deletion operation can be performed, but the computational cost becomes extremely high especially for single row deletions
Solution Approach 1:
The patent applies preliminary action by pre-building a mapping structure that stores the correspondence between row keys and their physical storage locations before deletion operations occur. This mapping structure is constructed during data insertion or loading phases, so when a deletion is needed, the system can quickly locate the row using the pre-computed mapping rather than scanning the entire table. This resolves the contradiction by preparing the access path in advance, making single-row deletions efficient without requiring traditional indexes.
2Speed
If traditional indexes are implemented to enable efficient row access, then individual row retrieval becomes fast, but the maintenance cost and complexity increase significantly for very large datasets
Solution Approach 1:
The patent applies segmentation by dividing the indexing function into two separate components: a lightweight mapping structure that stores key-to-location associations, and the main data storage. This mapping structure is much simpler than traditional B-Tree indexes and requires minimal maintenance. The segmentation allows the system to achieve fast row access through the simple mapping lookup while avoiding the complex maintenance overhead of traditional indexes, especially during data loading and deletion operations.
Solution Approach 2:
The patent introduces an intermediary mapping structure that acts as a mediator between the row key and the physical storage location. This mapping structure is a simplified data structure that directly associates keys with locations without the complex hierarchical structure of traditional indexes. The intermediary enables fast access while requiring minimal maintenance, resolving the contradiction between access speed and maintenance complexity.
3Reliability
If bulk synchronization is used to keep the analytical database updated with the operational database, then data consistency is maintained, but real-time analytics requirements are not met due to coarse-grained time intervals
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing the mapping between row keys and physical locations during data insertion or loading phases. This preparation enables the system to handle individual row deletions efficiently without requiring full table scans. As a result, the analytical database can be synchronized with the operational database at finer time intervals, meeting real-time analytics requirements while maintaining data consistency. The pre-computed mapping structure allows the system to efficiently process incremental updates rather than requiring bulk synchronization.
Data Source
AI summary
A method for deleting a row from a table in a database system comprises logically deleting the row in the first table in the database system by inserting a key of the row into a corresponding row of a dedicated table in the database system; querying the dedicated table during a query against the first table to identify the corresponding row in the dedicated table; and in response to identifying the corresponding row in the dedicated table, deleting the row from the first table and the corresponding row from the dedicated table as part of query processing during a subsequent query.