Re: SELECT DISTINCT(columname) and TEMP dbs

David Williams wrote: > > 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. PSORT_NPROCS is now used to set the number of sort threads, as you say, but based on the number of btappender threads used. The number of btappender threads used is set by OnLine based on the projected number of pages, as follows: Projected Number Number of of Index Pages btappenders <400 1 400 - 4k 2 4k - 40k 3 40k - 200k 4 200k - 1 million 5 >1 million 6 So this example would probably end up using 6 btappender threads and 12 sort threads if PSORT_NPROCS is set to 2. "same as DBSPACETEMP" is a bit of a sweeping statement. Setting PSORT_DBTEMP will over-ride the DBSPACETEMP setting and do sorting in the file system instead of in your dbspaces. Each directory listed in PSORT_DBTEMP will contain one fragment of the temp table, just as each dbspace listed in DBSPACETEMP will. Just be careful not to fill up any file system. ;-) Cheers, -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+