Oracle Delete Statement Internal Architecture

In this article we are going to learn how oracle delete statement work internally.

We explain here how the following delete statement work internally. 


Step 1: When user issues a delete command server process receive the delete statement from client and before executing delete statement server process required the validation check of the delete statement. Sever process check the syntax and semantic of the delete statement whether the statement is valid or not.

Now we are going to explain what is the syntax and semantic check with an example.

Syntax validation:

DELETE Employee WHERE empid= ‘10004’;

Output: Ora-00971: missing FROM keyword

Syntax validates check means whether the sql statement is correct or not. Above query syntax validation check fail as FROM keyword is missing of the delete query.

Semantic Validation:

Semantic validate check means whether database object is exist or not in database server. Following delete query semantic validation check fail as table name is incorrect (Correct Table: Employee).

DELETE FROM Employee1 WHERE empid= ‘10004’;

Output: ORA-00942: table or view does not exist

Step 2: After pass the syntax / Semantic check then server process go to the library cache and server process will search the delete statement in library cache for match the statement. If delete statement match in library cache server process use this statement and didn’t generate an execution plan this is called soft parse. On the other hand if delete statement don’t match in library cache then server process will generate multiple execution plan and this process is called hard parse. Server process will picked up the best possible and cost effective execution plan and go the next step.


Step 3: After parsing phase server process execution of the delete will began. Server process will bring the requested data block from corresponding datafile of the table into database buffer cache whose rows must be deleted.

Step 4: Blocks will contain the original data of the table. As the same time server process bring same number of empty block from the rollback/undo tablespace into the database buffer cache. Server process will copy the original data block from the userdata block into the empty rollback/undo block and create a before image. This rollback/undo block will be reserve for the current transaction and no other server process can’t use the rollback/undo block until current transaction is completed. Besides, userdata block in database cache will be lock no other server process can’t perform delete, update operation until current statement is being completed. This scenario is called row level lock.

Step 5: Server process will bring the set of user data blocks into PGA. Once filter process perform selected rows will be deleted that means data will be removed from original data blocks. This process will continue till all the userdata blocks has been checked and removed.

Step 6: Now user have two options commit/rollback if user issue commits oracle background process logwriter immediately writes all changes redo log buffer to disk and SCN number will generate. At the same time database buffer cache and rollback/undo will be release. During the next checkpoint dbwriter will write the data back to the respective datafile. If user issue rollback original data will be copy from rollback/undo into database buffer cache and userdata will be same as before.



Post a Comment