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; and
storing the new information in the database object in a non-transitory computer usable medium or displaying the new information on a display device.
2. The method of claim 1 , wherein identifying the memory occupied by the logically deleted row comprises:
determining that a row modification timestamp for the logically deleted row is less than a delete timestamp for the database object.
3. The method of claim 2 , wherein the row modification timestamp and the delete timestamp comprise System Change Numbers.
4. The method of claim 2 , wherein the row modification timestamp and the delete timestamp comprise realtime information.
5. The method of claim 2 , wherein the row modification timestamp and the delete timestamp do not contain realtime information.
6. The method of claim 2 , wherein the row modification timestamp and the delete timestamp comprise information describing the sequence of various operations.
7. The method of claim 1 , wherein the undo information is sufficient to recreate the deleted row.
8. A computer program product comprising a non-transitory computer usable medium having executable code to execute a process by a processor for performing a write operation on a memory occupied by a logically deleted row of a database object, the process 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; and
storing the new information in the database object or displaying the new information on a display device.
9. The computer program product of claim 8 , wherein identifying the memory occupied by the logically deleted row comprises:
determining that a row modification timestamp for the logically deleted row is less than a delete timestamp for the database object.
10. The computer program product of claim 9 , wherein the row modification timestamp and the delete timestamp comprise System Change Numbers.
11. The computer program product of claim 9 , wherein the row modification timestamp and the delete timestamp comprise realtime information.
12. The computer program product of claim 9 , wherein the row modification timestamp and the delete timestamp do not contain realtime information.
13. The computer program product of claim 9 , wherein the row modification timestamp and the delete timestamp comprise information describing the sequence of various operations.
14. The computer program product of claim 8 , wherein the undo information is sufficient to recreate the deleted row.
15. A system for performing a write operation on a memory occupied by a logically deleted row of a database object, comprising:
a computer processor to execute a set of program code instructions, in which the program code instructions comprises program code to:
identify 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;
determine whether the logically deleted row is older than an undo retention time;
store 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;
write new information to the memory occupied by the logically deleted row of the database object; and
store the new information in the database object in a non-transitory computer usable medium or displaying the new information on a display device.
16. The system of claim 15 , wherein identifying the memory occupied by the logically deleted row comprises:
determining that a row modification timestamp for the logically deleted row is less than a delete timestamp for the database object.
17. The system of claim 16 , wherein the row modification timestamp and the delete timestamp comprise System Change Numbers.
18. The system of claim 16 , wherein the row modification timestamp and the delete timestamp comprise realtime information.
19. The system of claim 16 , wherein the row modification timestamp and the delete timestamp do not contain realtime information.
20. The system of claim 16 , wherein the row modification timestamp and the delete timestamp comprise information describing the sequence of various operations.
21. The system of claim 15 , wherein the undo information is sufficient to recreate the deleted row.