extents and temp tables

Hi. I'got a problem: I'm use SELECT * INTO TEMP statement which results in big temp table. Next , I want add an index onto this temp table, but sometimes an error occurs: 229 cannot add index 136 no more extents Is any way to determine extents size or number for tables those are created by SELECT INTO TEMP??? I'm working with Informix 7.31 FC4 on HPUX11. Thank in advance. Andrzej Kurowski.

Hi Andrzei ! To determine params you want, query table sysmaster:systabinfo. For example, number of extents can be determined as follows : SELECT ti_next FROM SYSTABLES a, SYSMASTER:SYSTABINFO b WHEREa.partnum=b.ti_partnum and a.tabname='your_temp_table' You can also run 'oncheck -pT database:table_name' to determine number and size of your segments. ---------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285 Andrzej Kurowski <kuraprok@friko5.onet.pl> wrote in message news:7uem1b$t74$1@netra.daewoo.com.pl... > Hi. > I'got a problem: > I'm use SELECT * INTO TEMP statement which results in big temp table. Next , > I want add an index onto this temp table, but sometimes an error occurs: > 229 cannot add index > 136 no more extents > Is any way to determine extents size or number for tables those are created > by SELECT INTO TEMP??? > I'm working with Informix 7.31 FC4 on HPUX11. > Thank in advance. > Andrzej Kurowski. > >

Andrzej Kurowski wrote: > > Hi. > I'got a problem: > I'm use SELECT * INTO TEMP statement which results in big temp table. Next , > I want add an index onto this temp table, but sometimes an error occurs: > 229 cannot add index > 136 no more extents > Is any way to determine extents size or number for tables those are created > by SELECT INTO TEMP??? > I'm working with Informix 7.31 FC4 on HPUX11. Implicit temp tables, such as those created by an INTO TEMP clause are created with default extent size (16K) and depend on extent doubling and extent compression to prevent the problem you are seeing. If many temp tables are being created it is not possible to prevent large numbers of extents in such a temp table. Your best solution is to create the temp table explicitely, declaring EXTENT and NEXT sizes and INSERT INTO the table instead, thus: CREATE TEMP TABLE mytmp ... WITH NO LOG EXTENT SIZE 10000 NEXT SIZE 1000; INSERT INTO mytmp SELECT * FROM sometable; CREATE INDEX i_mytmp ON mytmp (...); Art S. Kagel