Step-2

Optimization

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 a subquery that requires optimization. It is a process in which multiple query execution plans for satisfying a query are examined and the most efficient query plan is satisfied for execution. The database catalog stores the execution plans and then the optimizer passes the lowest-cost plan for execution. 

Row Source Generation 

Row Source Generation is software that receives an optimal execution plan from the optimizer and produces an iterative execution plan that is usable by the rest of the database. The iterative plan is the binary program that, when executed by the SQL engine, produces the result set.

Query Processing includes translations of high-level Queries into low-level expressions that can be used at the physical level of the file system, query optimization, and actual execution of the query to get the actual result. 

High-level queries are converted into low-level expressions during query processing. It is a methodical procedure that can be applied at the physical level of the file system, during query optimization, and when the query is actually executed to obtain the result.

It needs a basic understanding of relational algebra and file organization. It includes the variety of tasks involved in getting data out of the database. It consists of converting high-level database language queries into expressions that can be used at the file system’s physical level.

The process of extracting data from a database is called query processing. It requires several steps to retrieve the data from the database during query processing. The actions involved actions are:

1. Parsing and translation
2. Optimization
3. Evaluation

The Block Diagram of Query Processing is as: 

 A detailed Diagram is drawn as: 

It is done in the following steps:

Parsing

During the parse call, the database performs the following checks: Syntax check, Semantic check, and Shared pool check, after converting the query into relational algebra because certain activities for data retrieval are included in query processing. First, high-level database languages like SQL are used to translate the user queries that have been provided. It is transformed into expressions that can be applied further at the file system’s physical level. Following this, the queries are actually evaluated along with a number of query-optimizing transformations. Consequently, a computer system must convert a query into a language that is readable and understandable by humans before processing it. Therefore, the best option for humans is SQL or Structured Query Language.

Parser performs the following checks (refer to the detailed diagram):

Syntax check: concludes SQL syntactic validity. 

Example:

SELECT * FORM employee

Here, the error of the wrong spelling of FROM is given by this check.