Hi David, Don't know if this is possible or not but couldn't you write your COMPLETE records to a .HIST file, keeping the rest of the data in a .CURR file. Then have a PART file definition that binds the two parts into what is the current filename? You could then index and select off the .CURR file and be able to process off either the PART filename or the .CURR filename.
I'm not real familiar with PART files but it seems like it should work. I'm not even sure if PART files are available in UD but thought the suggestion was worth mentioning. Good luck, Bob Woodward Senior Programmer/Analyst Harbor Wholesale Grocery -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Wolverton Sent: Wednesday, August 02, 2006 6:53 AM To: u2-users@listserver.u2ug.org Subject: RE: [U2] [UD] Indexing 'Best Practices' 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/ ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/