Hi, A general point on indexes. You should be careful in choosing the data that you index on. Indexing on a data field where a substantial number of records in the file have the same value in that field can cause performance problems.
An extreme example is a field that can contain Y or N. But it can happen with other fields, for example a field that contains state codes, or even a field that contains customer codes, but many of the record are for the same customer. Look at the distribution of the records for each value in the field that you are indexing on. Also consider using the NONULLs option when you build the index. As far as your EMC question is concerned, if you change the pathname of the file or the index files then you will get problems. You will need to use the SET.INDEX verb to get around that. cheers, asvin [email protected] Sent by: [email protected] Jul 07 2009 19:57 Mail Size: 8099 Please respond to U2 Users List <[email protected]> To "U2 Users List" <[email protected]> cc Subject [U2] General guidelines on indexing Entity Investment Banking Europe - IBEU 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 [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users ************************************************************ HSBC Bank plc may be solicited in the course of its placement efforts for a new issue, by investment clients of the firm for whom the Bank as a firm already provides other services. It may equally decide to allocate to its own proprietary book or with an associate of HSBC Group. This represents a potential conflict of interest. HSBC Bank plc has internal arrangements designed to ensure that the firm would give unbiased and full advice to the corporate finance client about the valuation and pricing of the offering as well as internal systems, controls and procedures to identify and manage conflicts of interest. HSBC Bank plc Registered Office: 8 Canada Square, London E14 5HQ, United Kingdom Registered in England - Number 14259 Authorised and regulated by the Financial Services Authority. ************************************************************ ----------------------------------------- SAVE PAPER - THINK BEFORE YOU PRINT! This transmission has been issued by a member of the HSBC Group "HSBC" for the information of the addressee only and should not be reproduced and/or distributed to any other person. Each page attached hereto must be read in conjunction with any disclaimer which forms part of it. Unless otherwise stated, this transmission is neither an offer nor the solicitation of an offer to sell or purchase any investment. Its contents are based on information obtained from sources believed to be reliable but HSBC makes no representation and accepts no responsibility or liability as to its completeness or accuracy. _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
