Database Bulk Delete Optimization via SCN Markers
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current bulk delete operations in database systems are time-consuming and inefficient, particularly for large database objects, as they require storing undo information for each row, which limits latency and prevents consistent reads and flashbacks.
Innovation Solution
Implementing System Change Numbers (SCNs) to record the relative times of database operations, allowing the deletion of all rows with minimal changes to the database object and no undo information storage, thereby reducing the number of writes to the undo tablespace and enabling efficient retrieval of deleted data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a bulk delete operation stores undo information for each row, then consistent reads and flashbacks are maintained, but the runtime and latency increase linearly with the number of rows
Solution Approach 1:
The patent extracts the undo information storage requirement from the bulk delete operation itself. Instead of storing undo records for each deleted row during the delete operation, the system uses a marker-based approach where only a single SCN value is stored in the table header. This separates the delete operation from the undo information storage, allowing the bulk delete to complete quickly while still enabling consistent reads and flashbacks when needed.
Solution Approach 2:
The patent changes the parameter of how undo information is represented. Rather than storing detailed undo records for each row, the system stores a single SCN (System Change Number) parameter in the table header that marks the point in time when the bulk delete occurred. This parameter change transforms the undo information from row-level detailed records to a table-level timestamp marker, dramatically reducing storage requirements and operation time.
2Loss of time
If a truncate operation is used instead of bulk delete, then runtime is constant and latency is reduced, but consistent reads and flashbacks cannot be provided for deleted data
Solution Approach 1:
The patent introduces an intermediary marker (the Delete SCN stored in the table header) that mediates between the truncate operation's speed and the flashback operation's data recovery capability. This marker acts as a reference point that allows the system to determine whether flashback is needed without requiring detailed undo records to be stored during the delete operation itself.
Solution Approach 2:
The patent performs a preliminary action of storing the Delete SCN marker in the table header during the bulk delete operation. This preliminary storage of the SCN value enables future flashback operations to determine whether and how to retrieve deleted data, without requiring the detailed undo information to be stored at the time of deletion.
3Reliability
If undo records are stored for each row, then data recovery is enabled, but the undo tablespace usage increases linearly with the number of rows
Solution Approach 1:
The patent extracts the essential recovery information (the SCN value) from the detailed undo records and stores only this compact representation in the table header. This extraction reduces the undo tablespace usage from linear proportion to the number of rows to a constant size regardless of the number of rows deleted.
Solution Approach 2:
The patent uses a cheap, compact SCN marker instead of expensive, large undo records. The SCN is a simple numeric value that serves as a disposable marker to enable recovery decisions, replacing the need to store large amounts of detailed undo information in the undo tablespace.
Data Source
AI summary
A bulk delete operation with reduced latency allows for retrieval of deleted data. Each database object holds a Delete SCN indicating when a bulk delete operation was last performed. Each row in the database object holds a Row Modification SCN indicating when the row was last updated. A bulk delete is performed by writing the old value of the Delete SCN to the undo tablespace and updating the Delete SCN. No undo information is stored for the rows. A write is performed by finding a deleted row, storing undo information for the deleted row and writing over the deleted row. To read from the database object, a rollback operation is performed, if necessary. Those rows are then retrieved for which the Row Modification SCN is higher than the Delete SCN and is less than or equal to the timestamp for the requested data.