QOD 11/20
-
Interesting that the answer is to effectively sort the entire physical table on a column that has low selectivity, thus guaranteeing deadlocks and scaling problems, when the query specified probably would not return any rows anyway.
I agree with the general statement that accompanies the ‘correct’ answer. But it shows that if you blindly follow general principles without understanding the data in the particular table, you will often make an incorrect choice.
In this case I will assume that the last_ordr_date is indeed the date of the last order. Such a physical piece of data would rarely fall in the future, but the query that is giving problems is selecting dates that are in the future. Based on that the query should return few rows and a simple non-clustered index would give the best all around performance.
-
This actually is QOD 11/21
I agree the question is contrived (no context, no joins to consider), but for the info given, I thought the answer was clear. As for "blindly following general principles", one must still know the general principles to make an informed choice.
A non-clustered index would have a large cost in such a large table. There's no guaranty it would improve query performance, my guess is it wouldn't, and it would certainly hurt insert performance (admittedly not part of the question).
Currently I'm one of only 7 out of 33 who have answered correctly. I have nothing against those who read the forum, or the e-mail before answering QODs they are unsure of - hey, this isn't MSCE. However, it would still be interesting to list % for QODs at different times after release.
Data: Easy to spill, hard to clean up!
-
Sorry, but if I create a PK clustered index, doesn't that mean that Last order date must be unqique? How that that be in any realworld context.
Must be some business....
-
It doesn't say create a PK clustered index. It says drop the PK clustered index and create a clustered index on the LAST_ORDER_DATE column. There's no mention of or necessity for uniqueness on LAST_ORDER_DATE.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
But I guess re-reading the wording of the question, it's a matter of interpretation
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
quote:
Drop the primary key clustered index and create a new one on LAST_ORDER_DTThis is somewhat ambiguous. Acuracy and precision are requirements for tech specs, woolly guff is expected in user requirements.
http://www.theregister.co.uk/content/7/34095.html
It should say
Drop the primary key clustered index and recreate the primary key as a nonclustered index, having done so create a clustered index on LAST_ORDER_DT.
-
You're absolutely right! It was only when I re-read the question that I realised I had completely (and blindly!) assumed the middle part of that. Maybe I'm just too used to having to read between the lines & interpret those woolly user requirements
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
< whine>
I want my points back....:(
< /whine>
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply