Friday, 6 April 2018

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

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

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.