select count distinct on indexed column

Hi, I've got a very simple query of the form: select count(distinct col1) from tab1 where col1 is the first column of a three column composite index. The table is round robin fragmented, the index is not fragmented, but is (of course) detached. The query is run using pdq and rather than scanning the leaf pages of the index, it parallel scans the table. This requires a huge (and slow) sort to create the distinct list of values in col1. Why does it do this, when the distinct values are in the index? Platform: HP-UX 10.20, 4 cpu HP-K series server, IDS 7.30UC7 Thanks in advance, Martyn martyn.hodgson@zurich.co.uk

I ran a similiar test on a fragmented (by expression) table in our system. It also showed a parallel scan of all fragments, but since the index was not fragmented, it's no bother. It showed a key-only scan of the index in question. No difference whether PDQ was turned on or off. How are your statistics on that column? John Carlson Informix DBA WHSmith USA Martyn Hodgson wrote: > > Hi, > > I've got a very simple query of the form: > > select count(distinct col1) from tab1> > where col1 is the first column of a three column composite index. The table > is round robin fragmented, the index is not fragmented, but is (of course) > detached. The query is run using pdq and rather than scanning the leaf pages > of the index, it parallel scans the table. This requires a huge (and slow) > sort to create the distinct list of values in col1. Why does it do this, > when the distinct values are in the index? > > Platform: HP-UX 10.20, 4 cpu HP-K series server, IDS 7.30UC7 > > Thanks in advance, > > Martyn > > martyn.hodgson@zurich.co.uk