How Select Statement Works

How Select Statement Works

When Oracle receives a sql query, it requires to run some pre-tasks before actually being able to really run the query. Combination of these tasks is called parsing.

During parsing the below operations used to perform.

Database validate the syntax of the statement whether the query is valid or not.For example, the following statement fails because the keyword FROM is missed:
Select employee where name=’RAM’;
Select employee where name=’RAM’
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Database validate the semantic of the statement.It checks whether a statement is meaningful or not.For an example, whether the objects and columns in the statement exist or not.
Although the statement is syntactically correct, but it can fail in semantic check.
SELECT * FROM exist;
SELECT * FROM exist
*
ERROR at line 1:
ORA-00942: table or view does not exist

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).

Hard parse:
If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse.

Soft Parse:
If the submitted 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 approach the optimizer, who will read the sql statement and generate the execution plan of the query. the optimizer generates multiple execution plans during parsing.
After generation of the execution plan’s by the optimizer the server process will take the best and cost effective execution plan and go to the library cache.
In the library cache the server process will keep the execution plan along with the original sql text.
At this point in time the parsing ends and the execution of the sql sataement will begin.
Server Process will then go to the database buffer cache and checks whether the data required by the query is already available or not in the cache.
If available that data can be returned to the client else it brings the data from the database files.

Row Source Generation:
The row source generator is software that receives the optimal execution plan from the optimizer and produces an iterative execution plan that is usable by the rest of the database.Each iterative execution plan is a binary program which is executed by SQL engine and it produces resultset.