Re: Is there way to build a compound index?

Perhaps I was not clear enough.I will try to be more lucid. I need the road name as a key and the aka road name as a separate key within the same index or an equivelant way to perform this task.Setting the key as (road name,aka road name) does not accomplish this. The search would involve several thousand records, so temp tables and in memory sorts would be too slow. Thanks for your time. Ex. record 1 Road Name-Central Ave Aka Road Number-Farmer Street I need to index keys for this record to be: Central Ave Farmer Street Obnoxio The Clown <obnoxio@hotmail.com> wrote in message news:8j7ajo$s10$1@news.xmission.com... > > From: "Mike Ratliff" <mike_ratliff@iname.com> > > > >I have a table with a road name field and an AKA(also known as) name field. > >Is there a way to get both names in a single index?. > >I can not think of a way to do it. The last programming language(ezc) > >allowed you to build any index key on a record that you wanted - about its > >only redeeming plus. > >Help if you can. > > Wow. That's amazing. Unless you consider: > > CREATE INDEX foo ON mytable (road_name, aka_name);> > ? > > Looks like a visit to my favourite porn site is in order, too: > > http://www.informix.com/answers > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com >

So, you want to be able to search where RoadName="Farmer Street" and get the "Central Ave" records, since "Farmer Street" is an alternate name for "Central Ave". Is that right? If so, I don't think you can do that without having a seperate "names" table with a many-to-one relationship with your "roads" table. Then do your name search on the "names" table joining the "roads" table. Looks like you've got a repeating group in your table the way it is currently setup, if I'm understanding you right. "Mike Ratliff" <mike_ratliff@iname.com> wrote in message news:bNI55.1917$yP5.90508@news2.mia... > Perhaps I was not clear enough.I will try to be more lucid. > I need the road name as a key and the aka road name as a separate key within > the same index or an equivelant way to perform this task.Setting the key as > (road name,aka road name) does not accomplish this. The search would > involve several thousand records, so temp tables and in memory sorts would > be too slow. Thanks for your time. > Ex. record 1 Road Name-Central Ave Aka Road Number-Farmer Street > I need to index keys for this record to be: > Central Ave > Farmer Street

OK, I understand. We have a similar problem with our phone entries database. We want to look up fold by their name, alternative name (ie Peter Wong may also be know as Yieh-Chen Wong or Jacob Solomon as Yaakov Solomon), but also by any standard nicknames (ie for William: Will, Bill, Wm, Willem, etc.). The alternate names part of the solution is what you need. Create a table with the AKA names and primary key of the main table. Index table AKA on (akaname, road_key) and road table on (road_name, road_key) AND (road_key). Now to select all roads known by the name "Farmer Street": SELECT * FROM road WHERE road_name = "Farmer Street" UNION SELECT r.* FROM road r, aka a WHERE r.road_key = a.road_key AND aka_name = "Farmer Street"; This UNION tends to generate better query plans than a straight join with an OR clause or a sub-query and is more parallelizable (is that a word?). The first part of the UNION will use the road_name index and the second part will use the road_key index. No single query could take advantage of both indexes (except in 8.xx which can use multiple indexes on a single table in a single query) and so either the direct search or the join would suffer. Art S. Kagel Mike Ratliff wrote: > > Perhaps I was not clear enough.I will try to be more lucid. > I need the road name as a key and the aka road name as a separate key within > the same index or an equivelant way to perform this task.Setting the key as > (road name,aka road name) does not accomplish this. The search would > involve several thousand records, so temp tables and in memory sorts would > be too slow. Thanks for your time. > Ex. record 1 Road Name-Central Ave Aka Road Number-Farmer Street > I need to index keys for this record to be: > Central Ave > Farmer Street > > Obnoxio The Clown <obnoxio@hotmail.com> wrote in message > news:8j7ajo$s10$1@news.xmission.com... > > > > From: "Mike Ratliff" <mike_ratliff@iname.com> > > > > > >I have a table with a road name field and an AKA(also known as) name > field. > > >Is there a way to get both names in a single index?. > > >I can not think of a way to do it. The last programming language(ezc) > > >allowed you to build any index key on a record that you wanted - about > its > > >only redeeming plus. > > >Help if you can. > > > > Wow. That's amazing. Unless you consider: > > > > CREATE INDEX foo ON mytable (road_name, aka_name);> > > > ? > > > > Looks like a visit to my favourite porn site is in order, too: > > > > http://www.informix.com/answers > > ________________________________________________________________________ > > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > >