In this article you will learn how delete statement works in oracle architecture. To understand execution of delete query internally, you must have the fundamental knowledge of oracle database architecture.
Flowchart along with the execution steps are mentioned below:
In previous article we discussed about the execution of select statement and up-to the generation of execution plan all the steps are same as select execution.
Please check here: Execution plan of select execution.
1. After generation of e-plan server process will keep the plan in the library cache on the most recently used end.
2. Thereafter the plan is picked up and execution of the delete will begin.
3. Server Process will bring the required blocks from the specific datafiles of the table whose rows must be deleted.
4. The blocks will be brought into database block buffers.
5. The blocks will be containing the original data of the table.
6. Then S.P. will bring equal no of empty blocks from the rollback/undo tablespace into the database block buffers.
7. SP will copy the orginal data from the userdata blocks into the empty rollback/undo blocks and create a before image.
8. Then SP will bring a set of userdata blocks into the PGA and after performing filter operations the selected rows will be deleted i.e.the data will be erased from the original data blocks.
9. The above delete process will continue until all the userdata blocks have been checked and erased.
10. After the delete operation is complete then dbwriter will write the data back to the respective datafiles after a certain time gap.