Re: How does Informix Read/Search Indexes?

UPDATE STATISTICS? From: sscheifler@my-deja.com > >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. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com