Re: SELECT DISTINCT(columname) and TEMP dbs
In article <5lfn8b$42k@cssun.mathcs.emory.edu>, Jeff Craig <settler@emor
y.mathcs.emory.edu> writes
>
>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???
>
Probably on the sorting required to gaurantee uniqueness
Try setting the PSORT_NPROCS (Number of sort threads) and
PSORT_DBTEMP (same as DBSPACETEMP but for sorting) environment
variables.
>-------------------------------------------------------------------
>Jeff Craig | dbINTELLECT Technologies, Inc.
>Senior Consultant | Golden, CO
> | (303) 275-6954 ext. 8008
> | email: settler@dbintellect.com
> | fax: (303) 275-2134
>-------------------------------------------------------------------
--
David Williams