Framework to optimize delete all row operations on database objects to improve throughput, query ability and flashback

摘要

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.

主权项

1. A computer-implemented method of performing a write operation on a memory occupied by a logically deleted row of a database object, comprising:
identifying, by using a processor, the memory occupied by the logically deleted row of the database object for writing, wherein the logically deleted row was logically deleted from the database object in a previous logical collective delete operation without placing undo information for the logically deleted row data into an undo log, such that the logically deleted row is still present in the database object after its logical deletion and the logically deleted row could have been retrieved from the database object during a rollback operation without retrieving the undo information for the logically deleted row from the undo log, wherein the write operation occurs after the previous logical delete operation and placement of the undo information for the logically deleted row into the undo log is deferred until the write operation; determining whether the logically deleted row is older than an undo retention time; storing the undo information for the logically deleted row in the undo log in response to the write operation being requested on the memory occupied by the logically deleted row, wherein the undo information is stored based on the determination that the logically deleted row is not older than the undo retention time; writing new information to the memory occupied by the logically deleted row of the database object; andstoring the new information in the database object in a non-transitory computer usable medium or displaying the new information on a display device.