Multiple choice

Consider a relational table $r$ with sufficient number of records, having attributes $A_1, A_2, \dots ,A_n$ and let $1 \leq p \leq n$. Two queries $Q1$ and $Q2$ are given below.

$Q1: \pi_{A_1, \dots ,A_p} \left(\sigma_{A_p=c}\left(r\right)\right)$ where $c$ is a constant $Q2: \pi_{A_1, \dots ,A_p} \left(\sigma_{c_1 \leq A_p \leq c_2}\left(r\right)\right)$ where $c_1$ and $c_2$ are constants. The database can be configured to do ordered indexing on $A_p$ or hashing on $A_p$. Which of the following statements is TRUE?

  1. Ordered indexing will always outperform hashing for both queries.

  2. Hashing will always outperform ordered indexing for both queries.

  3. Hashing will outperform ordered indexing on Q1, but not on Q2.

  4. Hashing will outperform ordered indexing on Q2, but not on Q1.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Q1 uses equality (A_p = c) - hash indexing is optimal for exact match queries (O(1) average case). Q2 uses range query (c1 ≤ A_p ≤ c2) - ordered indexing (B+ tree) is better for range queries as it can efficiently traverse adjacent entries. Hash indexing cannot support range queries efficiently. Therefore, hashing outperforms on Q1 but not on Q2.

Keep practicing — related questions