How Insert Statement Works

How Insert Statement Works

When Oracle receives sql/insert 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 insert job will start.

Server process will bring empty blocks from respective datafile of tablespace in which table exist and into which rows will be inserted.Blocks will be brought into database buffer cache.Blocks does not contain any data.

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.

Server process will bring set of userdata blocks and data will be added from the insert sql statement into user data blocks.

After the insert job completes in the database buffer cache, then database writer will write the data back to respective datafiles.