>Hey all,
>
>Had a curious discovery today and was wondering if anyone had an explanation or similar experience...
>
>I am converting some old data and consolidating it as I go. I am doing this via SQL INSERT statements into a combined table. Well, the table got big...around 2.3 million records. There are also a goodly number if indexes on these records. The DBF file ended up around 166 megabytes, and the CDX file ended up around 113 megabytes. No big deal.
>
>But then I tried a query. A fully optimized (according to SYS(3054)) query took 35 seconds to run locally and return about 15,000 records. Yikes, thinks I, but that is an awful big table.
>
>So, I pare down the table to about 1.9 million records by deleting and packing a bunch of records. This causes a reindex, naturally. The same query now takes under a second. Very strange. When I look at my CDX, I see it is down to a meager 56 MB (a 50% decrease on only a %17 record reduction).
>
>So, I start all over. I start with the big table again. Again, the CDX is 113 MB in size, and the query takes a long time. I then simply do a REINDEX on the table -- the CDX drops to 67 MB in size, and queries run in sub-second times.
>
>Am I just dense? Has the requirement of reindexing a large table after building it with INSERTs just common knowledge that I should know? What is going on with this CDX bloat when using INSERT to add so many records? How big of a table does one need to have before noticing this issue? Any and all comments would be greatly appreciated!
>
>Thanks,
>JoeK


Nope, that's the way it works. REINDEX will simply tacks the new keys at the end of the CDX and not reuse the old space. This is called Index Bloat. You need to DELETE TAG or DELETE TAG ALL before doing and INDEX ON (At this point, REINDEX will not work). There is a KB article on my web site that explains this.

Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer