The SQL query execution is a process of executing the SQL statements to fetch the data from the database. The SQL query processing undergoes into several steps such as below
- SQL Query
- Parser or Translator
- Optimizer
- Execution Engine
All these steps are important to convert, the SQL query into expressions and return the data from the database. See the below diagram of Query Processing, it has 4 main part.
1. SQL Query
The SQL query is a statement, which you write on the query console to get the data from the database.
2. Parser or Translator
Once you executed the query, it goes to the next step that is Parser & Translator. It will translate the high-level queries into low-level expressions, which is also known as relational algebra.
Following example shows how the query converted into relational algebra after translating process.
The SQL query
SELECT * FROM student where student_name='Joan';
Following expressions after converted into relational algebra
σ student_name = "Joan" (student)
Once this conversion is done, the next step is parse call, During this parse call, the database performs few checks such as Syntax check, Semantic check and Shared pool check.
I. Syntax check
determines whether any syntax issue in query or not
II. Semantic check
determines whether the statement is meaningful or not
III. Shared Pool check
Every query generates a hash code during its execution. So, this check determines the existence of written hash code in the shared pool. If hash code exists in the shared pool then the database will not take additional steps for optimization and execution. Follow shows flow in detail.
3. Optimizer
During the optimization stage, the database must perform a hard parse at least for one unique DML statement and perform optimization during this parse. This database never optimizes DDL unless it includes a DML component such as subquery that require optimization.
It is a process in which multiple query execution plan for satisfying a query are examined and the most efficient query plan is satisfied for execution.
Database catalogue stores the execution plans and then optimizer pass the lowest cost plan for execution.
4. Execution Engine
Finally runs the query and display the required result.
