Consider the following query: SELECT E.eno, COUNT(*) FROM Employees E GROUP BY…

2017

Consider the following query:

SELECT E.eno, COUNT(*)
FROM Employees E
GROUP BY E.eno

If an index on eno is available, the query can be answered by scanning only the index if:

  1. A.

    the index is only hash and clustered

  2. B.

    the index is only B+tree and clustered

  3. C.

    index can be hash or B+ tree and clustered or non-clustered

  4. D.

    index can be hash or B+ tree and clustered

Attempted by 196 students.

Show answer & explanation

Correct answer: C

CONCEPT: An index-only scan (a covering-index scan) lets the database engine answer a query purely from an index's own entries, without visiting the base-table rows. This is possible exactly when every column the query needs is already present in the index's key/leaf entries - a condition that depends only on WHICH COLUMNS the index carries, not on the index's internal storage structure (hash vs. B+ tree) or its physical organization (clustered vs. non-clustered), since both are just different ways of storing the same key values.

APPLICATION: Apply this to the query above.

  1. The query selects only two things: E.eno (the grouping column) and COUNT(*) (a count of rows per group).

  2. COUNT(*) needs no column value at all - it only needs to know how many rows fall in each eno group.

  3. So the only data the engine must be able to read is the eno values themselves, and those are exactly the key values stored in any index built on eno.

  4. A B+-tree index stores sorted eno entries; a hash index stores eno entries by hash bucket - either structure lets the engine enumerate every eno value and tally a count per value, so the structure type does not matter here.

  5. Whether that index is clustered (leaf pages hold the actual table rows, ordered by eno) or non-clustered (a separate structure that only holds eno values and pointers) also makes no difference, because the query never needs any column beyond eno - so the engine never needs to follow a pointer back into the base table.

CROSS-CHECK: Consider what would force a base-table lookup instead - if the query needed some other column of Employees (for example E.salary) that is not part of the eno index, then no matter the index's type or organization, the engine would have to dereference into the heap to fetch it. Since this query needs nothing beyond eno and a row count, that situation never arises here, confirming the index alone suffices under any hash/B+-tree, clustered/non-clustered combination.

Result: The condition does not depend on index type or organization, so the query can be answered by scanning only the index whenever index can be hash or B+ tree and clustered or non-clustered.

Explore the full course: Isro

Loading lesson…