Examine and compare the new query plans for these queries with the previous quer

Question

Examine and compare the new query plans for these queries with the previous query plans from Q4; briefly summarize your observations below.

Previous----------------------------------

composite index: CREATE INDEX birdIndex ON Bird(first_name, last_name);

Q4

After----------------

Two separate indexes

CREATE INDEX Bird_firstName ON Bird(first_name);

CREATE INDEX Bird_lastName ON Bird(last_name);

i) SELECT FROM Bird WHERE Bird first name Elizabeth and Bird last name Smith Query cost: 1.20 que ry block #1 1.2 1 row Non-Unique Key Lookup Bird birdlndex ii) SELECT FROM Bird WHERE Bird.first name Elizabeth Query cost: 1.20 query block #1 1.2 1 row Non-Unique Key Lookup Bird birdlndex iii) SELECT FROM Bird WHERE Bird.last name Smith Query cost: 5.80 query block #1 5.8 24 rows Full Table Scan Bird

Explanation / Answer

When you define a composite index on first name and last name both, the values are sorted as per the first name and after that if the first name is same then those entries are sorted using last name.

So, to find something from this composite index take some more time if the entry is not found on the first occurrence itself. For example, if you specify both the first and last name, it becomes a unique entry and it takes less time which is 1.2 here. When you are searching for some first name it can also be done in the same time because... as I wrote above, in this composite key, the first sorting is done on first name. So, you can consider it as a direct index on first name so it gives same result as previous. If you want to search based only on the last name, so you need to search on both i.e. the complete table. So, it takes more time than former. This time varies as per the number of records. Here it takes almost 5 times more than the single indexing.

In the modified code, you have given 2 separate index on first name and last name.

So, either you index on the first name or the last name or both, you need the same and less amount of time as it was taken in the previous code.

So, you can summarize it as: when the number of entries are less, if you are giving a composite index, you can achieve the desired result in less amount of time. But, when the number of entries are more in the table, you should create the single index for individual columns so that the required query processing time is less and we can do further task in speed.

Please comment if there is any query. Thank you. :)

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.