question

Senn-4458 avatar image
0 Votes"
Senn-4458 asked DanGuzman commented

Indexing when is it bad?

Hi everyone,

Why is this bad? The answer is also given but I really don't get it well. Can you simply explain with an example what is meant? I thought yes because you have to care about all query which are greater than 20. So the first 1-20 are not relevant for the user. But it seems another reasoning. Is it because its an heap = unclustered that its not worth to have an index because its unordered. So it doesn't make sense to have a range for? If I have heap then its better to have equal search then its way faster?

Consider a relation stored as a randomly ordered file and a single unclustered index on the field sal of the relation. If you want to retrieve all records with sal > 20, is using the index always the best alternative? Explain.

No. In this case, the index is unclustered, each qualifying data entry could contain an rid that points to a distinct data page, leading to as many data page I/Os as the number of data entries that match the range query. In this situation, using index is actually worse than file scan.

sql-server-generalazure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please edit the question and start with the explanation of what you speak about. It seems like you respond to something (post to an article) and nothing is clear in your message. If you respond to something then add a link to the source. and make the title count! what do you mean by indexing and what "range" is relevant here to the question (cluster or non cluster an maybe filtered index or all of these which mean to wide discussion for a thread in the forum). Try to focus the question a bit more for specific case

0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered DanGuzman commented

It seems that you are taking a course or a class on relational databases in general. The forum you have landed in specifically targeted for SQL Server, and what is right for SQL Server may be not be correct for another product.

For the given question, the answer is indeed "no", but then answer seems to forget why. "No", is correct because the question has the word "always". Without that "always", the answer would be "it depends". If, say, 1% of the rows in the relation has sal > 20, using the index is the best alternative. If 50% of the rows fulfil this condition, this is a lousy alternative, exactly for the reasons given in the answer. You will have to visit the same page many times, whereas when doing a scan, you would only visit it once. And, by the way, heap or clustered index, is not very important here. The important thing is that the index on sal is nonclustered.

What is also important is whether we actually need to visit the data pages. Say that we are only looking for a count. In that case, there is no need to go to the data page, and the index will work fine.

Now, keep in mind that these are not decisions that you as an SQL programmer have to make. These are decisions the optimizer makes when determining the most efficient way to compute the query. And as long as the optimizer does its job, you don't have to bother. This does certainly not always happen, and you may have to help it, by adding indexes or other means, and in this case it is good to has some understanding of when an index may help or not. But I am not sure that should be taught in a class where you also learn to write queries. Performance tuning is something you very much learn through practical experience.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your answer! Do you know a good forum to ask SQL query, content related questions?

0 Votes 0 ·

I would guess most SQL forums are related to a certain product, since after all, most people use a certain product which they need help with. But I have not made any research.

The obvious person to ask would be your professor, if you have one. If this is some self-study course where you cannot really interact with the author, it may be more difficult.

0 Votes 0 ·

Ok, I see!

0 Votes 0 ·

Do you know a good forum to ask SQL query, content related questions

For general ISO SQL standard query questions, stackoverflow.com with the sql tag is appropriate but you will get better and more accurate answers with a specific DBMS product tag. A DBMS-specific tag is required when it comes to implementation details like indexes, clustered indexes, heaps etc. because each DBMS product is different.


0 Votes 0 ·