index usages

Hi, If there are multiple indexes on a table and these indexes are used in a select query.Does informix choose the best index out of it or it can choose all the indexes and perform filtering at this level and do the remaining processing. For example if i have a table Tab1(a,b,c) and indexes on b and c. Data rowid a b c 1 1 1 1 2 1 1 2 3 1 1 3 4 1 2 3 5 1 2 3 6 1 3 2 7 1 3 2 8 1 3 2 9 1 4 3 10 1 5 3 11 1 6 3 I write a select query as select * from tab1 where b=1 and c=2 In this case does it always choose one index either b or c, then apply the other filter or else it has the capability of choosing the index on b with value=1 and choose index on c with value=2. When it chooses b=1 it has rowid's(1,2,3) and after choosing c it has rowid value of 2.Then take the common rowid =2 and go for a scan on the table.

Informix will only apply one index on any single table to a query in IDS 5, 7, or 9 (only IDS 8 could use multiple indexes). The remaining filters are either applied to the data record or, if the index contains the filter column but some additional keys intervene the engine can also perform a key-first filtering filtering the additional column(s) using the index nodes without fetching the data pages. So, if you have an index on (a, b, c) and a filter 'where a=3 and c=4' IDS will lookup a in that index then filter for c by skipping over the value of b. In your example you would want a compound index on (b, c). Art S. Kagel ----- Original Message ----- From: Parameshwar.... <pcdudyala@yahoo.com> At: 8/30 11:22 Hi, If there are multiple indexes on a table and these indexes are used in a select query.Does informix choose the best index out of it or it can choose all the indexes and perform filtering at this level and do the remaining processing. For example if i have a table Tab1(a,b,c) and indexes on b and c. Data rowid a b c 1 1 1 1 2 1 1 2 3 1 1 3 4 1 2 3 5 1 2 3 6 1 3 2 7 1 3 2 8 1 3 2 9 1 4 3 10 1 5 3 11 1 6 3 I write a select query as select * from tab1 where b=1 and c=2 In this case does it always choose one index either b or c, then apply the other filter or else it has the capability of choosing the index on b with value=1 and choose index on c with value=2. When it chooses b=1 it has rowid's(1,2,3) and after choosing c it has rowid value of 2.Then take the common rowid =2 and go for a scan on the table.

Hi, do you ask for DB2, don't you? DB2 has a feature called dynamic bitmap index ANDing which does what you want (under certain circumstances). Informix IDS chooses one index (depending on statistics, I presume), selects all fitting rows and applies the second criteria as filter to the rows afterwards. You can verify that by using set explain on (I didn't do that - I hope you don't prove me wrong). If that's a problem for you (big intermediate result sets) you can use a composite index (b,c) or think about bitmap indexes. Regards, Andreas Kutsche > ------------------------------------------- SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastrasse 3 A - 5015 Salzburg Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im > Auftrag von PARAMESHWAR.... > Gesendet: Dienstag, 30. August 2005 12:07 > An: ids@iiug.org > Betreff: index usages [5678] > > > Hi, > If there are multiple indexes on a table and these indexes are used > in a select query.Does informix choose the best index out of it or > it can choose all the indexes and perform filtering at this level > and do the remaining processing. > > For example if i have a table Tab1(a,b,c) and indexes on b and c. > Data > rowid a b c > 1 1 1 1 > 2 1 1 2 > 3 1 1 3 > 4 1 2 3 > 5 1 2 3 > 6 1 3 2 > 7 1 3 2 > 8 1 3 2 > 9 1 4 3 > 10 1 5 3 > 11 1 6 3 > > I write a select query as > select * from tab1 where b=1 and c=2> > In this case does it always choose one index either b or c, > then apply the other filter or else it has the capability of > choosing the index on b with value=1 and choose index on c with > value=2. When it chooses b=1 it has rowid's(1,2,3) and after > choosing c it > has rowid value of 2.Then take the common rowid =2 and go for > a scan on the table. > > > > >