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