AW: composite indexes and filter

Hi, Actually the database server can use the index for for these filters. However, it cannot use it to access the data rows directly. Rather the IDS server will have to go down the levels of the index branch nodes on the left side (WHERE a=12) and then then scan all the index leaf pages (WHERE a>=12) and check all key items whether second condition is true (WHERE b=15). Depending on the avail statistics the database server might decide that a sequential scan or use of a different index is less costly. Regards Tilman > -----Ursprüngliche Nachricht----- > Von: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org] Im Auftrag von Jörg > Rüschenschmidt > Gesendet: 11 January 2006 22:48 > An: informix-list@iiug.org > Betreff: composite indexes and filter > > Hi, > > can someone explain me why a composite index can not be used > for following filter when index idx(a, b ,c) is defined. > > WHERE a>=12 AND b=15 > > I refer to IBM document: > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp > ?topic=/com.ibm.perf.doc/perf381.htm > > Regards ... > > Joerg > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

>Depending on the avail statistics the database server might decide >that a sequential scan or use of a different index is less costly. Yes, the devil is in the details. If Informix decides that it would return more records than some X % of the table it will do a sequential scan. I have been told numbers as low as between 10 % and 15 %, which I find reasonable. Does Informix use a percent or does it factor in Seek and Rotational Latency? If you assume that a B-Tree index is scattered arround on your disk for every node (page) you read you have to wait for Seek and Rotational Latency. For table scans the pages are grouped together so you don't have to factor in Seek and Rotation Latency to every page read just to the first read. Another concern is that a compound index with 3 columns is of course almost 3 times the size of an index with only 1 column. If the data is structured such that b and c don't really add much to the descrimination value of the index (low cardinality) and you have an index on column "a" by itself it may never use the a,b,c index. An example would be column "a" has 100,000 distinct values in a table of 1,000,000 rows while column b only has 25 distinct values in that same 1,000,000 rows. The first column gives you on average 10 rows to look at a reduction of 1,000,000 to 10, adding column b can't reduce the 10 rows much more at all so it is of no value in reducing the databases work and just adds to the number of B-Tree pages that Informix will have to fetch because a B-Tree on A will of course be much smaller than a B-Tree on columns A and B.