Peter,

I'm just now getting back after taking some time off.

"prudent" means that it should be done when it's necessary, when there is some perceptible performance enhancement to be achieved.

Disk I/O affects performance, I think people tend to forget this, seek time is measured in milliseconds. That's an eon compared to CPU and memory access speeds. Disk access across a network is even worse because you are competing for wire bandwidth and other users reading writing other files on that drive.

Deleted records have no effect on CDX size (unless you have an index on deleted()).

If a table undergos lots (yes, lots is hard to quantify) of record additions, then the CDX will become badly fragmented both internally (all of the nodes of a tag are no longer contiguously stored inside the CDX) and externally (physical disk clusters). When a tag is created all of the nodes are placed together inside the CDX. As records are added additional nodes get added to the end of the CDX. When you have several tags you will see that the nodes of a tag now have a huge block in one place in the CDX and then single nodes scattered around at the end of the CDX. When a tree node splits half of the keys are moved to the new node. Say you have a full node that contains the keys for first 100 records, it's right at the beginning of the CDX file. Insert a new record where the key falls inside this 100 key range so the node must split. Now only the first 50 keys stay at the beginning of the file, the other 51 (the original 50 plus the new key) are moved out to a new node at the end of the CDX. It now takes significantly longer to retrieve the first 100 keys of this tag. This is because the you are no longer doing a single disk head position and read, two seeks and two reads are required. And you are adding a FAT (or equivalent) request to the O/S to even know where that cluster of the CDX is located. This all adds up.

I don't have a good "feel" for what percentage of inserts it takes before you start noticing the degradation. That needs to be determined on a case by case basis. If the users start complaining about how long some reports are taking, or how long it takes to bring up a form that shows child records, a reindex operation may help. If the reindex takes a long time to run schedule it to run off hours once a week or once a month.

Your application should have a reindex to fix any possible CDX corruption (records go missing, reports give wrong values, app crashes) that might happen if it ever does. That's the only time your users should have an expectation that it affects the correctness of the data.

If your app is delivered and it takes 20 seconds to run a report, and after a couple of months it now takes 50 seconds to run the report for the same amount of data, maybe a reindex would help. If it does reduce the time back to 20 seconds then cool you've given them the ability to keep the system performance tuned.

>The original statement of DavidF was that "bloat is something that affects performance" and that therefore "routine reindexing is prudent [...]". I have serious doubts about this position, well okay, under 'normal' circumstances. By the way, let not this thread become another discussion about bloat itself please. For that's not the issue I'm interested in, for now. Although bloat certainly is an issue if very large cdx-files are about to fill up an entire disk, in my case I'm only interested in the performance (=speed) and 'correctness' issues of (re)indexing.
>My view is: Under normal circumstances, REINDEX, or its drastic equivalent, will NOT improve performance. Therefore there's no need to advice superusers to regularly start the menu-option that will reindex all tables. The only time such an advice is ok, is when the SUPERuser has the feeling that an index has become outdated or corrupt. But even then it's often an even better advice to make a call to the helpdesk or developer.
>In other words, a reindex menu-option gives the user a false feeling of control over speed and correctness. Furthermore, while the superuser reindexes, other users cannot use the application. Sometimes that'll take minutes, in other applications it may take an hour or so. Precious time ... for nothing but false expectations.