>>then call the Time To Run Query = t, and
>>let's say x% of the records are deleted.
>
>>With indexing on deleted, performance will improve by:
>
>>Time To Run Query = .01 * (100-x) * t
>
>Simular rules also should apply to normal indexed fields for example:
>If a table contains a sex field where the value could be "M" or "F" this field can be compared with the deleted() mark of a record.
>
>IOW the optimizer makes no distiction between the deleted mark or a regular field.
No doubt you are right. But normally, this isn't an issue, that's why its worth looking at this time function now. That is, if we have a large table with a "lastname" field, and queries will be keyed on this field, we MUST have an index, whether the result set is 0 or 10,000 matches. But with deleted, we are in the opposite role, of having to decide whether or not to add an index, based on the predicted number of matches that could occur.
The next step would be to decide at what point of maximum predicted x (from above) is it of value to add the index, considering the index overhead (space, possible slowing of queries). 10? Something like that, maybe...
The Anonymous Bureaucrat,
and frankly, quite content not to be
a member of either major US political party.