From: cris@bs1.dsg.ti.com (cris schramm)
Newsgroups: comp.databases.informix
Subject: Re: out-of-control /tmp files (fwd)
Date: 16 Nov 1992 11:33:04 -0500
Organization: Mailing List Gateway
Distribution: world
Reply-To: cris@bs1.dsg.ti.com (cris schramm)
NNTP-Posting-Host: emory.mathcs.emory.edu
X-Informix-List-ID: <list.1612>

> 
> Mark Trotter writes:
> >I don't have a SMP machine.  I will take a stab at guessing that most of the
> >machines running Informix are not SMP machines.  Using the file system for
> >data storage seems to be a step backwards on my machine, especially for
> >"larger sorts".  I don't have the room, and don't want the overhead.  How
> >can I get sorting back in the dbspace where it belongs?
> >
> David Kosenko writes (from Informix):
> >> You cannot "convince" the server to use raw space (dbspace space, actually)
> >> for sorts.  The "new" sort package was designed to perform parallel
> >> sorting on SMP machines.  This requires having different drives available
> >> for i/o when psorting is used.  It was decided that the best approach to
> >> this was to use Unix files for the sorting process (generally only used
> >> for larger sorts).
> 
> sorting will NOT use dbspace space, it will always
> use Unix file space.  You cannot "get sorting back in the dbspace."  The new
> sort package was designed to be able to take advantage of SMP machines, but
> works and is also used for single processor machines.
> 

Mark, 

I ran into this immediately with Informix 5.0 OnLine while trying to build
a TPC benchmark database with 6 million rows and generating an index.  I
used the DBTEMP environment redirecting temporary space to a partition that
had 50 MB of free space.  I almost ran out of space again because it took
46 MB of temporary space to create the index.  This was before I read the
release notes file that gave the NOSORTINDEX environment variable.  Hope
one of these solutions will help you.

This comes from the file $INFORMIXDIR/release/ENGREL_5.0:

        ADDITIONAL FEATURE FUNCTIONALITY
        ================================

        Sort Merge Joins
        ----------------

        Sort merge is a new table join technique implemented in Version 5.0.
        Sort merge join provides an alternative to the existing nested loop
        table join where temporary indexes are used.  The query optimizer
        will choose the best path of execution to produce the fastest
        possible join: either loop join, sort merge, or a combination of the
        two.  Loop joins will create a temporary table and index on the inner
        table of a join, if such an index does not already exist.  A sort
        merge join instead sorts either one or both of the tables involved
        in the join.  Consequently, if sort merge joins are chosen, sort temp
        space is required rather than temp space within dbspaces (in the
        case of INFORMIX-OnLine).  The sort temp space is determined by the
        value of DBTEMP.
 
and more:

        FAST INDEX BUILD
        ====================

        Informix Version 5.0 introduces a way to improve the performance
        for index creation on large tables.  Prior to OnLine Version 5.0,
        OnLine read each row in the table sequentially and added its key value
        to the index being built.

        The first step in creating an index on a large table now involves
        sorting the index key values.  This significantly reduces the amount
        of i/o during the index build.  Fast indexing is the default for
        tables with more than 500 rows and 30 OnLine data pages.  To disable
        fast indexing and revert to the previous method for index creation
        you can set NOSORTINDEX as an environment variable before you begin
        your session.

        The index key values are sorted using files in /tmp or another
        directory you can specify by setting DBTEMP.  There will be at
        least one file created whose size can be calculated as follows:

                (size of key value in bytes + 4) * number of rows in table
  
        For machines with multiple processors, the key value sort can
        benefit from the use of the parallel sort facility in Version 5.0.
        See the INFORMIX-OnLine Administrator's Guide for documentation on
        how to make use of this feature.
 
Regards,
Cris Schramm    Texas Instruments, Inc. 	     	#include <disclaimers>
                Internet                cris@bs1.dsg.ti.com