Re: Is there way to build a compound index?

Well, I'm glad *someone's* awake today. :-) From: "Art S. Kagel" <kagel@bloomberg.net> > >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 > > > ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com