How many disk blocks to be accessed to answer the SQL query

How many disk blocks to be accessed to answer the SQL query

Question:

Consider the following table:

EMPLOYEES (EmpID, Name, Salary)

The table is stored on a disk file consisting of 40 blocks. EmpID is the primary key and the primary key index is a B-Tree with 3 levels and 20 leaf nodes.

For each of the following queries, state how the query is to be executed (e.g., full table scan, full index scan, etc.) and calculate the associated cost (in number of blocks):

(i) SELECT empID FROM employees;

(ii) SELECT name FROM employees WHERE empID = 120;

(iii) SELECT * FROM employees WHERE salary > 15000;

Solution:

(i) SELECT empID FROM employees;

This query does not have a WHERE clause hence there is no filtering condition. Also, it projects only one attribute EmpID which is also the primary key. As per the given information, there is B-Tree index table on EmpID.

The query requests values stored in empID attribute alone. Therefore, no need to access the table on disk as all the data requested in the query is contained in the index. Hence, we need to do a full scan of the index and it will cost: number of leaf nodes = 20 blocks.

20 blocks to be scanned to produce the result for the given query.

(ii) SELECT name FROM employees WHERE empID = 120;

This query has a WHERE clause and the filtering condition (empID = 120) involves the key attribute. To locate the empID with the value 120, we need to scan the index table. As we use B-Tree index and the index values are unique, we need to scan the number of levels of the B-Tree and one leaf node where the request value is stored.

Hence, unique index scan does the following;

Number of levels to be scanned in the B-Tree + 1 leaf node = 3 + 1 = 4

4 blocks to be scanned to produce the result for the above query.

(iii) SELECT * FROM employees WHERE salary > 15000;

Given that there is no index on salary, a full table scan is performed. Cost = 40 blocks.

***********

Go to Solved Exercises in DBMS page

Go to Multiple Choice Questions in Disk Storage and Access page

 

find the number of disk blocks to be accessed to answer the SQL query

SQL query cost calculation

Index scan

Full index scan, full table scan

when do we need to perform full index scan

the occasions we need to go for full table scan

given a table, calculate the cost of execution of SQL query in terms of disk block access