ISRO-DEC2017-16

Question

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

• A. the index is only hash and clustered 
• B. the index is only $B+$ tree and clustered 
• C. index can be hash or $B+$ tree and clustered or non-clustered 
• D. index can be hash or $B+$ tree and clustered

Comments

@MiNiPanda explain this one

Answer 1

By hashing and chaining technique , we can group the index w.r.t primary keys

In clustering index we can group ordering field with a non key value(Note:One file can have one primary index or one clustering index, but not both)

In non clustering or secondary index  can be specified for any non ordering field of a file

$B^{+}$ tree work on non key field too, where grouping has been done with the branching of tree

Answer 2

answer is C

Comments

means it could be anything

even B tree can do this

---

what's the use of such Answers ?

---

exactly @aakashpreetam

Answer 3

Let's compare these indexing techniques.

Hash Tables	B Trees
Not suitable for range queries, since they don't keep keys in any order. So, only suitable for "=" queries (or point queries)	Suitable for range queries, since the keys are kept in order. So, suitable for  =, >, >=, <, <=, or BETWEEN operators (range queries)
Not Scalable	Scalable
More suitable for primary key based lookups.	Also suitable for primary key based lookups, but Hash Tables are better because of their Time Complexity.
O(1) performance if the hash function is chosen wisely,and Hash Table is sufficiently large. Otherwise degrades to O(n).	Always O(logn) performance.

Sources: This, this and this.

---

---

 the query can be answered by scanning only the index if

We just have to scan the index, not the Database file. Hash Table with Chaining, and B+ trees are fine.

Now, we have to output the row count of a specific eno. This can be done by looking at any index.

We aren't asked which will be faster; we're asked if it can be answered. Nor is it given that the query is a range query.

 the query can be answered by scanning only the index if

So, Option C