Any perfrmant solution for this type of Query ?

Hello, we are seeking a high performance query that allows us to retrieve rows by using in it two incompletely filled fields. Can you help us ? Here is the problem : we have an Informix table that contains the two following fields : field1 & field2. We would like to perform a query using this two fields, but with none of them completely filled. Only the first characters of field1 & field2 are filled; So it is impossible to use an index that contains the two fields to retrieve efficiently the rows wanted. In practice, this query will allow to select clients whose name begins with "AB" for instance and whose postal code begins with "1". Is there an high performance query (or an high performance definition of the indexes) that can solve this problem ? Thank you for your help Jean-Francois DENIS Siemens Business Services email: jean-francois.denis@sni.be

try " select * from table where field1 like 'abc%' and field2 like 'def%' " when using "like" % acts as the *-wildcard in Dos and Unix shells, where _ acts as the ?-wildcard. Hope this helps Silvio Bierman J.-F. Denis wrote in message <3694C7C5.3175@sni.be>... >Hello, > >we are seeking a high performance query that allows us to retrieve rows >by using in it two incompletely filled fields. >Can you help us ? > >Here is the problem : >we have an Informix table that contains the two following fields : >field1 & field2. We would like to perform a query using this two fields, >but with none of them completely filled. Only the first characters of >field1 & field2 are filled; So it is impossible to use an index that >contains the two fields to retrieve efficiently the rows wanted. >In practice, this query will allow to select clients whose name begins >with "AB" for instance and whose postal code begins with "1". > >Is there an high performance query (or an high performance definition of >the indexes) that can solve this problem ? > >Thank you for your help > > > >Jean-Francois DENIS > >Siemens Business Services >email: jean-francois.denis@sni.be

J.-F. Denis wrote: > > Hello, > > we are seeking a high performance query that allows us to retrieve rows > by using in it two incompletely filled fields. > Can you help us ? > > Here is the problem : > we have an Informix table that contains the two following fields : > field1 & field2. We would like to perform a query using this two fields, > but with none of them completely filled. Only the first characters of > field1 & field2 are filled; So it is impossible to use an index that > contains the two fields to retrieve efficiently the rows wanted. > In practice, this query will allow to select clients whose name begins > with "AB" for instance and whose postal code begins with "1". > > Is there an high performance query (or an high performance definition of > the indexes) that can solve this problem ? Informix IDS/XPO (Extended Parallel Option) supports using mulitple indexes in a single query which regular IDS cannot do. You could then index field1 and field2 in separate singleton indexes which the engine could use to filter the two MATCHES/LIKE/substring filters and then combine the results. Art S. Kagel

Silvio Bierman wrote: > > try " select * from table where field1 like 'abc%' and field2 like 'def%' " > You can also write, which may be better (test it and look at the set explain output): select * from table where field1[1,3] = 'abc' and field2[1,3] = 'def' > when using "like" % acts as the *-wildcard in Dos and Unix shells, where _ > acts as the ?-wildcard. > > Hope this helps > > Silvio Bierman > > J.-F. Denis wrote in message <3694C7C5.3175@sni.be>... > >Hello, > > > >we are seeking a high performance query that allows us to retrieve rows > >by using in it two incompletely filled fields. > >Can you help us ? > > > >Here is the problem : > >we have an Informix table that contains the two following fields : > >field1 & field2. We would like to perform a query using this two fields, > >but with none of them completely filled. Only the first characters of > >field1 & field2 are filled; So it is impossible to use an index that > >contains the two fields to retrieve efficiently the rows wanted. > >In practice, this query will allow to select clients whose name begins > >with "AB" for instance and whose postal code begins with "1". > > > >Is there an high performance query (or an high performance definition of > >the indexes) that can solve this problem ? > > > >Thank you for your help > > > > > > > >Jean-Francois DENIS > > > >Siemens Business Services > >email: jean-francois.denis@sni.be -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 -- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/

Depending on the size of the table you could try one of the following: Create another column in the table that is derived from the first couple of characters from field1 and field2. Index this column and use it as a filter (field3 = field1[1,2] & field2[1,2]). Use a separate table if you have a large row size in the existing table. Sean Kennedy Adaptec Inc. "J.-F. Denis" wrote: > Hello, > > we are seeking a high performance query that allows us to retrieve rows > by using in it two incompletely filled fields. > Can you help us ? > > Here is the problem : > we have an Informix table that contains the two following fields : > field1 & field2. We would like to perform a query using this two fields, > but with none of them completely filled. Only the first characters of > field1 & field2 are filled; So it is impossible to use an index that > contains the two fields to retrieve efficiently the rows wanted. > In practice, this query will allow to select clients whose name begins > with "AB" for instance and whose postal code begins with "1". > > Is there an high performance query (or an high performance definition of > the indexes) that can solve this problem ? > > Thank you for your help > > Jean-Francois DENIS > > Siemens Business Services > email: jean-francois.denis@sni.be