OK - sold! Virtual field to move COMPLETE to NULL is the answer... As I was driving back to the hotel after doing that post last night, I got to thinking that the Virtual Field computation would strictly be CPU/Memory, which means I can do A LOT of that for the cost of a single disk access. And with the B+Tree design, we are doomed to at least TWO disk reads in every case (unless you happen to hit the Root record!) - the first to the 'lead' block, and then a read either 'left' or 'right' - and fresh from sleep, I can see that as the index grew, that could turn into 3, 4, or 50 traversals (aka Disk reads) left or right to position the key on write. The leaf size is 4K - PERIOD. So every 100 or so records in a leaf, there would be some sort of shift of level push that would involve even more write traffic.
More than likely I'll create a new dictionary that is a Virtual Field, and index that NO.NULLS and change the routines that need expedited selects that we were using the index for anyway - any report that would allow "COMPLETE" as an option will probably have to go through the whole file, so we may have to create 'versions' of the report - a faster "Select Only From non-COMPLETE" Version that can use Indexes, and a slow, but inclusive "Can Include COMPLETE" version which will use the unindexed attribute. Thanks for all who responded on and off list! If anyone else knows magic that goes on behind the scenes on indexes, or has recommendations, please continue to chime in! ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
