How an SQL Query is executed by a Database Management System?
Introduction:In this post, I would like to discuss the Query Execution process in simple terms. The detailed diagram of Query Execution process is given in Figure 1. I have mentioned two main components in the figure, namely Component A (Primary Storage) and Component B (Secondary Storage). First of all, let us have an abstracted view on these components on their roles in the process of Query Execution.
A - Primary Storage:
- It verifies the syntax of the query,
- It checks for the table names and columns specified in the query against in Data Dictionary,
- It generates various possible cost based plans for the given query,
- It chooses one among the generated plans which would be best and,
- It instructs the Buffer manager to bring the required data into the buffer for execution.
- It checks for the availability of the requested block in buffer,
- If the requested block is not in the buffer, then allocates the space in the buffer,
- It then locates the data block in the disk, and transfers to the buffer, and
- It forwards the address of the block in main memory to the program which initiated the request.
B - Secondary Storage:
With this introduction, let us discuss the process with an example query in the following section.