How does Informix Read/Search Indexes?

This may sound like an easy question, but I'm looking for someone who can answer it with absolute confidence and authority as it pertains to Informix (7.31 UC2 on HP-UX10.20. if that's relevant here). I'm not a DBA, so please bear with me. Imagine a table (proj_resource) with 90+ columns and 8+ million rows. Two of those columns are Type and Status. Among the various indexes is one specifically on Type and Status only. The total number of distinct Types is 5. The total number of distinct Status' is 3. The total number of distinct combinations is 13 (Not all Types can have every Status). Now consider the following statements: select distinct Type from proj_resource; select distinct Type, Status from proj_resource; select Type, Status, count(*) from proj_resource group by 1, 2; In both cases, the optimizer uses the correct index and key-only access occurs. Unfortunately, it takes sever minutes to return the results for any of them. Here are the questions: Exactly how is the index searched and read? In the first case, should it not be able to 'skip' down the distinct values of the first level and return those in a blink of an eye? Even in the second statement, where counts are still not required, shouldn't it take at most a few seconds? (Remember, there are a total of only 13 distinct combinations) When getting counts as in the third statement, does it use rowid or other means to do the math of how many there are of each, or must it read the entire index? So, why so slow???? Thanks! SJS Sent via Deja.com http://www.deja.com/ Before you buy.

sscheifler@my-deja.com wrote: > > This may sound like an easy question, but I?m > looking for someone who can answer it with > absolute confidence and authority as it pertains > to Informix (7.31 UC2 on HP-UX10.20. if that?s > relevant here). > > I?m not a DBA, so please bear with me. > > Imagine a table (proj_resource) with 90+ columns > and 8+ million rows. Two of those columns are > Type and Status. > Among the various indexes is one specifically on > Type and Status only. > The total number of distinct Types is 5. > The total number of distinct Status? is 3. > The total number of distinct combinations is 13 > (Not all Types can have every Status). > > Now consider the following statements: > > select distinct Type from proj_resource;> > select distinct Type, Status from proj_resource;> > select Type, Status, count(*) from proj_resource > group by 1, 2;> > In both cases, the optimizer uses the correct > index and key-only access occurs. > Unfortunately, it takes sever minutes to return > the results for any of them. > > Here are the questions: > > Exactly how is the index searched and read? For non-unique indexes Informix uses a partially inverted index whereby for each unique value of a key there will be exactly one node and that node will point not to a data row but to a leaf page (or list of pages) containing the list of rowids containing that key. HOWEVER, four bytes are needed for each rowid and there are 1.6MILLION rowids, on average, with each type value. That means that LOTS of disk space has to be skipped to get from the node for type 5 to the node for type 6. > In the first case, should it not be able to > ?skip? down the distinct values of the first > level and return those in a blink of an eye? Sort of but since Informix expects to have to use those rowids eventually the index storage and IO routines are designed to draw in the leaf pages along with the nodes meaning that since, unless type is a VERY large column, all of the keys can fit on one node page ALL Of the leaf pages will be sucked in reading your index. For efficiency, see the Performance Guide, Informix recommends AGAINST creating indexes with a HIGH level of duplication. It is just not efficient. Try adding a more unique column to the END of the keys in all three of these indexes. If that makes the new key accidentally unique then make the index a UNIQUE index which will save some disk space and invoke more efficient UNIQUE index code. > Even in the second statement, where counts are > still not required, shouldn?t it take at most a > few seconds? (Remember, there are a total of > only 13 distinct combinations) > > When getting counts as in the third statement, > does it use rowid or other means to do the math > of how many there are of each, or must it read > the entire index? > > So, why so slow???? -- Art S. Kagel & Family kagel@erols.com