SQL | Query Processing

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

Block Diagram of Query Processing is as:

Detailed Diagram is drawn as:

It is done in the following steps:

• Step-1:
  Parser: During parse call, the database performs the following checks- Syntax check, Semantic check and Shared pool check, after converting the query into relational algebra.

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

  1. Syntax check – concludes SQL syntactic validity. Example:

     SELECT * FORM employee 

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

  2. Semantic check – determines whether the statement is meaningful or not. Example: query contains a tablename which does not exist is checked by this check.
  3. Shared Pool check – Every query possess a hash code during its execution. So, this check determines existence of written hash code in shared pool if code exists in shared pool then database will not take additional steps for optimization and execution.

  Hard Parse and Soft Parse –
  If there is a fresh query and its hash code does not exist in shared pool then that query has to pass through from the additional steps known as hard parsing otherwise if hash code exists then query does not passes through additional steps. It just passes directly to execution engine (refer detailed diagram). This is known as soft parsing.
  Hard Parse includes following steps – Optimizer and Row source generation.

• Step-2:
  Optimizer: During optimization stage, database must perform a hard parse atleast 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 most efficient query plan is satisfied for execution.
  Database catalog stores the execution plans and then optimizer passes the lowest cost plan for execution.

  Row Source Generation –
  The Row Source Generation is a software that receives a 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 executes by the sql engine produces the result set.

• Step-3:
  Execution Engine: Finally runs the query and display the required result.