What are the steps of SQL Query processing in DBMS?

DBMSDatabaseBig Data Analytics

Query Processing is a translation of high-level queries into low-level expression. It is a step wise process that can be used at the physical level of the file system, query optimization and actual execution of the query to get the result.

It requires the basic concepts of relational algebra and file structure. It refers to the range of activities that are involved in extracting data from the database. It includes translation of queries in high-level database languages into expressions that can be implemented at the physical level of the file system.

Given below is the diagram that shows the steps of the structured query language (SQL) query processing −

Steps for SQL processing in database management system (DBMS) are as follows −

Step 1

Parser − While parsing, the database performs the checks like, Syntax check, Semantic check and Shared pool check, after converting the query into relational algebra.

  • The syntax check concludes SQL is syntactically correct or not, that means it checks SQL syntactic validity.

The syntax for parser to do syntax check is −

SELCT * FROM student;

The output is as follows −

error

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

  • Semantic check It determines whether the statement has meaning or not. Example: query contains a table name which does not exist is checked by this check.

  • Shared Pool check This check determines existence of written hash code in shared pool, suppose if code exists in shared pool then database will not take additional steps for optimization and execution because every query possess a hash code during its execution

Step 1

Optimizer − In this stage, the database has to perform a hard parse at least for one unique DML statement and it has to do optimization during this parse. This database never optimizes DDL unless it includes a DML component.

It is a process where multiple query execution plans 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 passes the lowest cost plan for execution.

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

Step 3

Execution Engine − Execution engine is helpful to run the query and display the required result.

raja
Published on 08-Jul-2021 08:15:16
Advertisements