It is sometimes possible to evaluate a particular query using only indexes, with
ID: 3702612 • Letter: I
Question
It is sometimes possible to evaluate a particular query using only indexes, without
accessing the actual data records.
Consider a database with two tables which includes information about dogs and people
who feed them:
People(SSN, Pname, State, Income)
Dogs(SSN, Dname, Breed)
Assume three unclustered indexes, where the leaf entries have the form [search-key
value, RID].
<Pname> on People
<SSN, Income> on People
<Dname, Breed> on Dogs
For each of the following queries, say whether it can be evaluated with just data from
the indexes (i.e. without going to the data records). If the query can be answered,
describe how. If the query can't, explain why.
(a)
SELECT MIN(Income)
FROM People;
(b)
SELECT State, MAX(Income)
FROM People
GROUP BY State;
(c)
SELECT Pname, COUNT(SSN)
FROM People
GROUP BY Pname;
(d)
SELECT COUNT(*)
FROM Dogs
WHERE Breed = ‘Poodle’
(e)
SELECT Pname
FROM People, Dogs
WHERE People.SSN = Dogs.SSN;
Explanation / Answer
We have two tables:
People(SSN, Pname, State, Income)
Dogs(SSN, Dname, Breed)
3 unclustered indexes on the above two tables :
<Pname> on People
<SSN, Income> on People
<Dname, Breed> on Dogs
Answer 1: SELECT MIN(Income) FROM People;
It will be able to produce results using indexes only. The second index <SSN, Income> on People will be used for providing the data for People.Income (Table_Name.Column_Name). And on the data the Min function can efficiently work.
Answer 2: SELECT State, MAX(Income) FROM People GROUP BY State;
This will not produce results by using just Indexes as there is not index for People.State The query can produce results till SELECT State, MAX(Income) FROM People using second index but GROUP BY State cannot be performed.
Answer 3: SELECT Pname, COUNT(SSN) FROM People GROUP BY Pname;
The query will produce results using the indexes. The index 1 and 2 on table People will be used to provide the data. Pname will e obtained from first index and SSN details are available from the second index. The results can be grouped by Pname.
Answer 4: SELECT COUNT(*) FROM Dogs WHERE Breed = ‘Poodle’
This query will produce results using the indexes only. The third index will be used to produce the results. Once we get the records for Dname and Breed, we use the WHERE cluse on Breed and perform the count based on the results.
Answer 5: SELECT Pname FROM People, Dogs WHERE People.SSN = Dogs.SSN;
This query will not be able to produce the results based on indexes only. There is no index on SSN column for both the table so, the indexes will not give results based on the indexes only. We will need to extract the data querying the table
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.