Re: SELECT DISTINCT(columname) and TEMP dbs

Or further, you can issue a simple select and pipe it to 'uniq' (or sort of) command. Vj David Henseler wrote: } } 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