I believe a long time Universe support person once told me that secondary indices are essentially just Type 25 files, or B-Tree files.
So when you build one, its essentially taking output from the query processor and squirreling that away in a Type 25 file at the OS level. So for example, I have an index on our CUSTOMER file for the Dictionary CUST-NAME At the OS level (assuming you are on a *nix system) The CUSTOMER file lives: /uvdata/accts/ACCOUNT/CUSTOMER And then the type 25 files just reside in: /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.001 /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.002 /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.003 /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.004 /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.MAP and so on... So if you have two Dictionaries like you have. One called NAME One called NAME.UPCASE which is an I-Type that just references NAME You should be just fine on building the index on NAME.UPCASE. I would not think you would have to build the index on both of them. You just need to build the index on the one you are referencing in your SELECT statement. The only disadvantage to going crazy with indexes is that it will take a longer time to do a bunch of writes or "copies" to the file because all of the indexes have to be updated. So for example, lets say you have to bulk load a ton of clients all at once (a few thousand lets say)... That process will increase with the amount of indexes you have, but, once the data is there, it can be retrieved very fast. Of course, I'm no U2 University Admin graduate, so don't quote me on this... but it seems in my experience that the above is true. On Tue, Jul 26, 2011 at 4:00 PM, George Gallen <ggal...@wyanokegroup.com>wrote: > I'd only index NAME if you are going to use it in any SELECTs that you want > optimized. We can't really answer that question. > > > -----Original Message----- > > From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- > > boun...@listserver.u2ug.org] On Behalf Of Chris Austin > > Sent: Tuesday, July 26, 2011 3:56 PM > > To: u2-users@listserver.u2ug.org > > Subject: Re: [U2] CONV MCU on indexed field? > > > > > > George, > > > > I just rebuilt the indices on the CLINE table, then tried the LIKE > > using the '...' as a wildcard, so I tried: > > > > SELECT CLIENT WITH NAME LIKE "BRUCE..." > > > > and unfortunately that worked the exact same way as the [] wildcards, > > returning 1 result. > > > > The approach I'm going to use is the one you suggested (to create an I- > > descriptor to do an UPCASE(NAME) ) and > > then index that. The only part I'm not sure about is since this > > descriptor is using the field NAME, should I also index > > NAME? > > > > I've indexed NAME already and seems to be working great, just not sure > > if indexing NAME should be done for performance. > > > > Chris > > > > > From: ggal...@wyanokegroup.com > > > To: u2-users@listserver.u2ug.org > > > Date: Tue, 26 Jul 2011 14:27:58 -0500 > > > Subject: Re: [U2] CONV MCU on indexed field? > > > > > > I think your running in PICK mode, and I know that LIKE works in PICK > > flavor as well. > > > > > > LIKE is the equivilent to [ and ] > > > > > > SELECT FILE WITH NAME = "[TEST" > > > SELECT FILE WITH NAME LIKE "...TEST" > > > > > > SELECT FILE WITH NAME = "TEST]" > > > SELECT FILE WITH NAME LIKE "TEST..." > > > > > > SELECT FILE WITH NAME = "[TEST]" > > > SELECT FILE WITH NAME LIKE "...TEST..." > > > > > > REPLACE THE [ OR ] WITH ... > > > AND REPLACE THE = WITH LIKE > > > > > > George > > > > > > _______________________________________________ > > 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 > -- John Thompson _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
