Register

GATE CSE 2011 | Question: 39

• edited by
9,922 views
41 votes
41 votes

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$
• edited by
User Avatar

6 Answers

Best answer
75 votes
75 votes

(C) Hashing works well on the 'equal' queries, while ordered indexing works well better on range queries too. For ex consider B+ Tree, once you have searched a key in B+ tree , you can find range of values via the block pointers pointing to another block of values on the leaf node level.

• edited by
User Avatar
5 votes
5 votes

Typically, ordered indexing is used unless it is known in advance that range queries will be infrequent, in which case hashing is used like Q2:πA1,…,Ap(σc1≤Ap≤c2(r)) . Hash organizations are particularly useful for temporary files created during query processing, if lookups on a key value are required and no ranges queries will be performed like Q1:πA1,…,Ap(σAp=c(r)).

c is correct

User Avatar
4 votes
4 votes

Hashing is generally better at retrieving records having a specified value of the key.

If range queries are common, ordered indices are to be preferred.

https://www.cse.iitb.ac.in/~sudarsha/db-book/slide-dir/ch12.pdf

User Avatar
Page:
Voting & Accepting an answer helps improve the community
Answer:
← PreviousNext →
← Previous in categoryNext in category →

Related questions

13.1k
views
1 answers
37 votes
go_editor asked Sep 29, 2014
13,089 views
GATE CSE 2011 | Question: 46
Database table by name $\text{Loan_Records}$ is given below.$$\begin{array}{|c|c|c|} \hline \textbf {Borrower} & \textbf {Bank_Manager} &\textbf {Loan_Amount} \\\hline \...
User Avatar
13.1k
views
4 answers
37 votes
go_editor asked Sep 29, 2014
13,064 views
GATE CSE 2011 | Question: 32
Consider a database table T containing two columns $\text{X}$ and $\text{Y}$ each of type $\text{integer}$. After the creation of the table, one record $\text{(X=1, Y=1)}...
User Avatar
17.0k
views
4 answers
53 votes
go_editor asked Sep 29, 2014
17,039 views
GATE CSE 2011 | Question: 12
Consider a relational table with a single record for each registered student with the following attributes:$\text {Registration_Num:}$ Unique registration number for each...
User Avatar
11.9k
views
6 answers
40 votes
go_editor asked Apr 21, 2016
11,925 views
GATE CSE 2011 | Question: 55
An undirected graph $G(V,E)$ contains $n \: (n>2)$ nodes named $v_1,v_2, \dots, v_n$. Two nodes $v_i, v_j$ are connected if and only if $ 0 < \mid i-j\mid \leq 2$. Each ...
User Avatar
Link Copied!