Good analysis... And, I agree with the aspect of a disproportanate number of deleted vs. non-deleted records. As I said, no rule is cast in stone. While in general, I think it is a good practice to have a tag on deleted records, there are exceptions. Just as there are times that you can get better performance by setting optimize off....

>John,
>
>Nope, VFP is not lying. Using an index on deleted() does probvide full optimization by Rushmore, and in fact that is what causes the deleted tag to slow things down under certain conditions.
>
>The problem with the deleted tag on very large data sets is not exclusive to the deleted tag, but rather is true for any tag that has a severely disporportional distribution of values. In order for Rushmore to optimize the statement it has to read the index for the records meeting the condition. If there is a table (to keep it simple I am referring to a straight file server based DBF stuff) with 100 Million records of which 1,000 are deleted. Then in order for Rushmore to optimise the deleted status it must read 99,999,000 records from the deleted index into memory, this may cause the use of virtual memory. The problem is not actually the deleted tag but is rather a severely disporportional distribution of values. That is, the same side effect would be seen if the index was on a logical field that was 99.99% true with only .01% false.
>
>The article in FoxPro Advisor that brought this to light had emperical tests using very large data sets that demonstrated that you are corect, sys(3054) did report partial optimization without the deleted tag. However, the tests also showed that the partially optimized statements ran faster than the fully optimized ones did.