2

I'm studying non-key organizations to know when are they better to use than classical key ones. With the following SQL queries, which of them takes less benefices from the multi-attribute index on R with A as a first attribute and B as the second attribute ?

SELECT * FROM R WHERE A = 10;
SELECT * FROM R WHERE B = 20;
SELECT * FROM R WHERE A >B;
SELECT * FROM R WHERE A>C;
SELECT * FROM R WHERE C<100 AND A =10;

I would say that those which aren't equality searches are those that may have less benefices from the multi-attribute index, but I'm not sure why...

Here is an an example of a multi-attribute index (which remains as a link for I don't know which reason ... :

Example of a multi-attribute index

2

I'm going to have to make a couple of assumptions. First that when you said + you meant =. Next that when you say less benefits you mean less benefits than the other options.

First lets say there are four indexes (based on your image)

R(Age)
R(Sal)
R(Age, Sal)
R(Sal, Age)

And re-writing your queries to match:

SELECT * FROM R WHERE Age = 10;
SELECT * FROM R WHERE Sal = 20;
SELECT * FROM R WHERE Age > Sal;
SELECT * FROM R WHERE Age > Col3;
SELECT * FROM R WHERE Col3 < 100 AND Age = 10;

First of all let's look at the easy ones:

SELECT * FROM R WHERE Age = 10;
SELECT * FROM R WHERE Sal = 20;
SELECT * FROM R WHERE Age > Col3;
SELECT * FROM R WHERE Col3 < 100 AND Age = 10;

The reason these are easy is because they only reference one or the other of the columns being indexed. That being the case I'm going to use one example to server for all of them.

SELECT * FROM R WHERE Age = 10;

In this case the R(Age) index will be best. It is the shortest possible index on that column. Next R(Age, Sal). This is more than sufficient because Age is still first in the list, meaning that seeks will still work or even a scan of everything between two points. The only down side here is that it is slightly longer and so a bit slower. The difference in speed however is pretty negligible so if I had a reason for creating R(Age, Sal) that's what I would create and I wouldn't mess with R(Age). Lastly R(Sal, Age)' might get used because it's smaller than the entire table, but it will still have to be scanned (just like the unindexed table) to get any useful information. I.E. If my indexed pairs for(Sal, Age)are(10,20), (11,5), (12,45), (12, 2)` and I'm looking for an Age of 45 I'm still going to have to scan through all of the pairs to get anywhere.

Now the last one (the one that references both columns)

SELECT * FROM R WHERE Age > Sal;

In this case the indexes R(Age, Sal) or R(Sal, Age) are equivalent. Both values are indexed and since both are in the expression it doesn't matter which comes first. Given the expression you are going to have to scan the index (there is no way to use the order of either value to help) but it's better than scanning the whole table. I'm honestly not sure if R(Age) or R(Sal) would be of any user or not. They contain some of the required information, but you still have to go back to the original table to get the remaining information. It might be faster just to scan the table itself.

Just to add an example of a query that would benefit from a multi-attribute index. Let's say your query looked like this:

SELECT * FROM R WHERE Age = 10 AND Sal = 40;

At this point the system can benefit from a multi-attribute index. It can seek to find all rows where Age = 10 and then seek in those rows for cases where Sal = 40. The best order (R(Age, Sal) vs R(Age, Sal)) is going to depend. For example: how many possible values are there? If there are 100 possible Ages, but only 5 possible values for Sal then R(Age, Sal) would probably work best. And vise-versa of course.

|improve this answer|||||
  • Thank you for this answer ! 1. Don't we always have to scan the tables when doing a query on it ? 2. Therefore, according to your point of view only SELECT * FROM R WHERE Age > Sal; would benefit from a multi-attribute index ? – Revolucion for Monica May 2 '17 at 16:41
  • Note: I'm speaking from a practical point of view, I don't have much of a theoretical background. But in general you have multiple structures. The index is actually separate from the table. Therefore you can actually just scan the index to find the rows you need if the index is appropriate. – Kenneth Fisher May 2 '17 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.