!!!!!!!!!!!....Examine and compare the query plans for this composite indexing e

Question

!!!!!!!!!!!....Examine and compare the query plans for this composite indexing example. As before, in one brief sentence, what can you observe about composite B+ tree indexes and their applicability to multi-attribute queries? (Enter your observation below.)

Before composite indexing been created it and you can solve it

CREATE INDEX birdIndex ON Bird(first_name, last_name);

After index been created it

i) SELECT FROM Bird WHERE Bird.first name Elizabeth and Bird.last name Smith Query oost: 5.800 query block #1 5.8 24 rows Full Table Scan Bird ii) SELECT FROM Bird WHERE Bird.first name Elizabeth Query cost 5.80 query block #1 5.8 24 rows. Full Table Scan Bird iii) SELECT FROM Bird WHERE Bird.last name Smith Query cost 580 query block 1 5.8 24 rows Full Table Scan Bird

Explanation / Answer

Before creating indexing, it has to scan whole table to get a match for paramters first_name and/or last_name in mentioned query.

So basically it will scan whole table row by row, compare value of first_name and/or last_name as mentioned in where cluase and fetch the number of records which should be returned to user.

But creating index will speed up the search. How?

Let's first concentrate on what is indexing?

The easiest way to think about an index is to think about a dictionary. It has words and it has definitions corresponding to those words. The dictionary has an index on "word" because when you go to a dictionary you want to look up a word quickly, then get its definition. A dictionary usually contains just one index - an index by word.

A database is analogous. When you have a bunch of data in the database, you will have certain ways that you want to get it out. Let's say you have a User table and you often look up a user by the FirstName column. Since this is an operation that you are doing often in your application, you should consider using an index on this column. That will create a structure in the database that is sorted, if you will, by that column, so that looking up something by first name is like looking up a word in a dictionary. If you didn't have this index you might need to look at ALL rows before you determine which ones have a specific FirstName. By adding an index, you have made this fast.

In above question, index has been created on a tuple of (first_name, last_name). If you have don't know which value of first_name that you are interested in, but only last_name, then it would have to read the whole index ("full index scan"), which is not as efficient, otherwise it will just look up in index and fetch the record faster. And that's why after creating index, your 1st and 2nd query run faster than before but 3rd query will take same time as full table scan still required.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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