Re: SELECT DISTINCT(columname) and TEMP dbs

Jeff Craig wrote: I am trying to execute a simple query such as: SELECT DISTINCT(columnname) FROM ANYTABLE ; There are approximately 40 million rows in ANYTABLE. The query runs forever, and the only activity I can see is major temporary dbspace reads and writes. I have tried forcing a light scan, but still hangs in the temporary dbspace thrash step. There are twenty temporary dbspaces on separate devices and controllers, raw. The system has sixteen CPUs, several gigabytes of memory. We are running INFORMIX-OnLine 7.20.UC2 on IRIX 6.2. ANYTABLE is on a single device. It is a two-column (rowsize of eight bytes) table. Statistics are fresh. UNIX sar command shows moderate activity on the temporary chunks, but no "pegged" disks. Anyone have any ideas on where and why this query is hanging??? Jeff: I think you are just maxing out the system but you could try these solutions. I don't know if you have the luxury of rebuilding the table but it seems to me that you have plenty of disks to break the i/o up. Try to fragment the table by round robin or expression and then set PDQPRIORITY = 1 to allow parallel disk i/o. This is another possible solution although not always practical, unload the table to a UFS and run a unix sort with a unique parameter. Good Luck. David Henseler