We are on Unidata 7.1 on Sun Solaris We have used indexing extensively in our database. We have one file with 10 indexes. The file is currently using around 80 gig. (35 dat, 9 idx and 1 Over parts files). I am not sure how to weigh the update performance difference these indexes make. But we feel whatever is lost updating is more than gained reporting wise.
After indexing, we made a lot more use of the SETINDEX and READFWD logic in our programs. This technique is used when there are lots of dictionary items used in evaluating what to select. Rather than do 'SELECT FILE WITH START.DATE > "01/01/09" AND WITH ORG.STATE = "MN" AND WITH DOLLAR.AMT > "2500.00" AND WITH REGION > "1" AND WITH QUALIFIER < "10", we would use the index on START.DATE to position us in the index at "01/01/09". READFWD then reads records into your program in START.DATE order. The rest of the conditions can then be evaluated in Unibasic to see if records should be selected. Records that meet the conditions are written to a well sized work file. DON'T USE A DYNAMIC ARRAY to build a list on a large file. A well size work file will run circles around a huge dynamic array. We have had processes that built arrays using READFWD to hold the keys take days to run. Switching to a well sized work file to hold the keys cut the process to hours. I have used BUILD.INDEX ONLINE on smaller files. IT takes a long time to build an index with people in it. But it does seem to work. The only data consistency problems we have had were due to crashes and indexes were missing data. There is a guide index verb to try and catch these problems. You can also find them using a SELECT with NO.INDEX to compare results to see if a problem is there. We have very little problem with this. We don't have Sub Values indexed. But we have values indexed all over. It seems to work fine if your are aware of using EVERY, EACH and a couple other Multi-value verbs. Unidata does not have that header problem that Universe has. If you move the file around, Unidata's indexes still work fine. Indexes can get you a lot of performance gain. It will cost you some disk space on large files. And your programmers need to understand when to do a EXECUTE SELECT and when to use SETINDEX/RFWD If you have other questions let me know. - Rod -----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of bradley.sch...@usbank.com Sent: Tuesday, July 07, 2009 1:57 PM To: U2 Users List Subject: [U2] General guidelines on indexing Our primary application hasn't needed the performance gains offered by indexing, but our database has grown large and complex enough that we're looking at it seriously. Having only dabbled with indexing in test environments, I've got a few general and best practice questions. I've seen some comments in the archives on some of the pitfalls, e.g. the dangers of indexing remote files and indexing on correlatives with subroutines. But posts I found are at least a year old. I'm guessing there may be updated information out there. Feel free to point out that everything I'm asking is in the archives and send me there. Some questions: * Are there guidelines for how many indices is too many for one file (assuming disk space isn't an issue)? * Does BUILD.INDEX with the ONLINE parameter work as advertised? Can it really be run while the file is being updated? * How about data consistency? I seem to remember there being concerns in earlier days of an index not always being updated correctly. * How about indexing multi/subvalued fields? I don't know that we'd want to, but is it advisable? Valuable? * We use EMC to clone our production account so we can run nightly reports off-line. The account is renamed in this process. Might that cause any index issues? The clone is read-only, so there are no updates, just queries. * I've seen mention of index corruption. Is it obvious when an index is corrupt or can it be subtle? If subtle, are there ways to detect issues before our users do? * Performance is what we're after, but are there benefits to indexing other than performance? AIX 5.3 UniData 7.1 All thoughts, comments, observations are most appreciated. Brad Schrag U.S. BANCORP made the following annotations --------------------------------------------------------------------- Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. --------------------------------------------------------------------- _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
