Jim,

Most CDX bloat is caused by the nonrecovery of space used by index tags that have been deleted. A lesser bloat is caused by B+ tree fragmentation caused by the normal insertion of records. I don't recall when the REINDEX command started recovering deleted tag space. I don't think it did back in FP2.x. It does in VFP5 and VFP6 which are the only versions I have on this machine. It is also quite possible that REINDEX effectively is doing 1) read the indexes, 2) delete tag all, issuing the individual index commands

This is the test code I used:

create table reindexsize ( i1 i, c1 c(20), d1 t, i2 i )

n = 10000
for i = 1 to n
   insert into reindexsize values ( i, str(i) + str(n-i), datetime(), n-i )
endfor

activate window "debug output"

index on i1 tag i1
debugout Stats()

index on c1 tag c1
debugout Stats()

delete tag i1
debugout Stats()

index on d1 tag d1
debugout Stats()

delete tag d1
debugout Stats()

index on i2 tag i2
debugout Stats()

reindex
debugout Stats()

delete tag all
index on c1 tag c1
index on i2 tag i2
debugout Stats()


function Stats()
local lcStr

lcStr = ""
for i = 1 to tagcount()
   lcStr = lcStr + "Tag = " + tag(i) + chr(13)
endfor

use
adir( laJunk, "reindexsize.cdx" )
lcStr = lcStr + "CDX size = " + str( laJunk[1,2] )
use reindexsize exclusive
return lcStr

>I post this mainly because it appears that many people believe that use of the REINDEX command causes CDX "bloat".
>
>I know that I have always believed so until Evan D. noticed that SP3 seemd to have a much faster REINDEX and such was confirmed by MS. At that time I wondered if that might have affected the known bloat effect, reasoning that doing all indexes in 1 pass means that all of the TAGS would be in storage at the same time and so the process might then be able to overwrite the CDX from its beginning.
>
>I finally tried this out yesterday and, sure enough, there was absolutely NO CDX BLOAT even after 5 REINDEX commands (closing the subject table each time in between).
>
>Knowing no better I attribute this to SP3.
>
>. . .but is that really the source of this observation. . .
>
>The only other FoxPro that I have on a machine is a FPD 2.6 (probably 2.6b). I made an adjustment to change 2 of the indexes from type I to type N and repeated the test there.
>
>To my great surprise there was NO CDX BLOAT there either!!!
>
>Personally, I find this to be real puzzling. As puzzling, in fact, as the "fact" that the TAG on DELETED() is now known to be detrimental in most circumstances AND was "proven" to be similar in effect in FPD and FPW (per the article in FPA some time back.
>
>Regardless, there are two primary reasons always stated as reasons to avoid the REINDEX command in favour of DELETE TAG ALL and recreate indexes:
>
>1) CDX bloat caused by REINDEX;
>2) REINDEX cannot work when the header is corrupted.
>
>It is undeniable that there is a requirement to always have all of the INDEX ON statements available in case a corrupted header necessitates full reconstruction of the CDX.
>
>BUT using the "DELETE TAG ALL and recreate indexes" technique as the STANDARD way to maintain indexes should definitely be re-evaluated, for two major reasons:
>
>1) There is no CDX bloat with REINDEX, so there is no penalty;
>2) REINDEX is much much faster since all indexes are rebuilt in a single pass where the other technique definitely causes multiple reads of the DBF.
>
>I believe that my simple test was adequate but stand ready to hear differently.
>
>I also would like to hear some reasoning as to why the FPD test would show similar results to the VFP test. I can say that the system with FPD on it does also have VFP with SP4 on it and do wonder if there might be some way that components of VFP could be used by FPD in such circumstances.
>
>JimN