ScottPletcher (1/31/2013)
startdttm was the only column used in all three of your sample queries. If you have other queries that are more representative of the normal work load than what you've provided us with as we try to assist, then of course go by the more represenative queries.
This was a query with a suprising effect. If you compare the 2 situation with the 3 situation you can see that the query is almost the same, but that for the 3 situation an extra field is used for selection.
compare:
2: Select * from A where B = 1
3: Select * from A where B = 1 and C = 2
What did suprise me that the second query was a ten fold more expensive. Both in estimated cost as in diskreads.
If for the situation 2 the number of diskreads is 4, for situation 3 the diskreads can always be done in the same number or less reads than in situation 2.
But the optimizer chooses to take another path this did supprise me.
Hence the question.
Ben