Re: How are temp tables stored?

George Teachman (george@talon.ftc.scs.ag.gov) wrote: > > > > Does anyone know how temporary tables are stored in Informix? I have > > heard that they are stored in memory if they are small enough, although > > it seems to me they would have to write out to disk beyond a certain > > size. Still their performance does not seem any better than regular > > tables, once they are created and have indexes in place. So I suspect > > they are really stored on disk anyway. Anybody know? I am using > > Informix 5, but would also like to know how 6 does it, if different. > > > > Thanks heaps > > > > Gerry Tynen > > ------------------------------------------------------------------------------- Long Description: With the advent of OnLine version 4.10, the question of temp table location was confused a bit. This article is meant to chart the waters of current OnLine versions in terms of SORT tables (often called sort files), IMPLICIT and EXPLICIT temporary tables. Temp Table Type Example +----------------- ------- | | S (Sort) SELECT * FROM customer ORDER BY lname,fname; | | I (Implicit) SELECT * FROM customer INTO TEMP temp1; | | X (Explicit) CREATE TEMP TABLE temp1 (column1 CHAR(80)); | Version ROOTDBS DBTEMP CURRENT DBSPACE 4.00 I,S X 4.10 I S X 5.00 I S X Notes: 1) CURRENT DBSPACE refers to the "home" dbspace of the open database. In other words, an Explicit temp table, just like a real table, is considered part of the current database. It is written along side its permanent siblings unless told to live elsewhere by an IN [dbspace] clause. 2) Beginning with version 4.1 you can combine Table Types I and S, like so: SELECT * FROM customer ORDER BY lname,fname INTO TEMP temp1; The chart above applies to this case too. The sort files generated by the ORDER BY will be created in DBTEMP, and temp1 will go into ROOTDBS. Incidentally, as soon as temp1 is completed in ROOTDBS, the sort files are deleted from DBTEMP. 3) An ORDER BY (not an ORDER EXTERNAL BY) in a 4GL report generates code to build an explicit temp table, then performs a SELECT with an ORDER BY on the temp table. Consistent with the above table, this temp table will be built in the current dbspace, and then sorting will be done in DBTEMP. 4) For multi-processors, there is another wrinkle which applies only to SORT-type temp tables beginning in 4.10. If one sets the PSORT_NPROCS environment variable (and one is on a multi-processing machine), Parallel Sorting is enabled. (PSORT_NPROCS should be set to the number of processors. Once Parallel Sorting (PSORT) is enabled, one can set the PSORT_DBTEMP environment variable to one or more full pathnames separated by colons. If OnLine chooses to use PSORT, it will use the directories specified in PSORT_DBTEMP in round-robin fashion. If PSORT_DBTEMP is not set, PSORT will use the paths specified in DBTEMP. Without DBTEMP or PSORT_DBTEMP set, OnLine will default to /tmp for all sorting in versions 4.10 and 5.00. ------------------------------------------------------------------------------- Regards, Maury maury@rc822.taiwan.hp.com