How Delete Statement Works

How Delete Statement Works

When Oracle receives sql/Delete query, it requires to run some pre-tasks before actually being able to really run the query.

During parsing, Database validate the syntax of the statement whether the query is valid or not.

Database validate the semantic of the statement. It checks whether a statement is meaningful or not.

If syntax/Semantic check pass, then server process will continue execution of the query.The server process will go to the library cache.In the library cache the server process will search from the MRU (Most Recently Used) end to the LRU (Least Recently Used) end for a match for the sql statement. It does this by using a hash algorithm that returns a hash value. If the hash value of the query we have written matches with that of the query in library cache then server process need not generate an execution plan (soft parsing) but if no match is found then server process has to proceed with the generation of execution plan (hard parsing).

As already mentioned above about semantic check, this check is done in the data dictionary cache by the server process. Server process will check the definition of the object, if that is already available within the data dictionary cache, server process will process the check. If not available then server process will retrieve required information from the system tablespace.

If the submitted sql statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This is called Soft parse.

In case of hard parsing the server process will check with the optimizer, because the optimizer will read the sql statement and generate the execution plan of the query. The optimizer generates multiple execution plans during parsing.

Once the execution plan generates by the optimizer, then the server process will pick the best possible and cost effective execution plan and go to the library cache.

Server process will keep the execution plan along with the original sql text in the library cache.Here the parsing ends and the execution of sql statement will start.

Server process will keep the plan in the library cache on the MRU (Most Recently Used) end after generation of execution plan. Then the plan is picked up and execution of the delete job will start.

Server process will bring the required blocks from respective datafile of tablespace in which table exist and which rows must be deleted.Blocks will be brought into database buffer cache.Blocks contain original data of the table.

Server process will bring same no of empty blocks from rollback/undo tablespace. Server process will copy the address of the actual data blocks of userdata datafiles into the empty rollback/undo blocks and a before image will be created.

Server process will bring set of userdata blocks.Once Filter operation completed, the selected rows will be deleted. That means data will be removed from original data blocks.

Above Delete process will continue till all the userdata blocks have been checked and removed.

Once the Delete job completes, DBWR(dbwriter) will write the data back to the respective datafiles.