Oninit cdi / Archive
Talk to an expert →

index on temporary table

Posted in 2005

Topics: Connectivity: ESQL/C, 4GL & Embedded SQL, Migration, Import/Export & Data Conversion, Versions, Editions & End-of-Life

DANIEL CANDAS — — source: IIUG Forums & Mailing Lists

We are using indexes on temporary tables, in programs written in Informix 4gl. After migration from Informix SE 7.31 to Informix IDS 9.40HC6, we notice ( by set explain ) that the temp tables are readed in sequential mode, instead of using index ;So the same program spent 20 mn on SE and 4 hours on IDS. Is there some parameters to change ? Thanks in advance.

↪ replying to DANIEL CANDAS
Walter Milan — — source: IIUG Forums & Mailing Lists

Run update statistics on the temp table after the index is created -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of DANIEL CANDAS Sent: Thursday, March 17, 2005 10:16 AM To: ids@iiug.org Subject: index on temporary table [4539] We are using indexes on temporary tables, in programs written in Informix 4gl. After migration from Informix SE 7.31 to Informix IDS 9.40HC6, we notice ( by set explain ) that the temp tables are readed in sequential mode, instead of using index ; So the same program spent 20 mn on SE and 4 hours on IDS. Is there some parameters to change ? Thanks in advance.

↪ replying to DANIEL CANDAS
Khaled Bentebal — — source: IIUG Forums & Mailing Lists

Hi, Temps tables should be manipulated the same way as regular tables as far as the optimizer is concerned. You should run update statistics on the temp table in your 4GL code after creating the indexes on the temp table if the number of rows in the temp is important. Regards, Khaled Bentebal ConsultiX Tél: 33 (0) 1 39 72 17 00 Fax: 33 (0) 1 39 72 17 01 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: http://www.consult-ix.fr ----- Original Message ----- From: "DANIEL CANDAS" <dcandas@leonfargues.com> To: <ids@iiug.org> Sent: Thursday, March 17, 2005 5:15 PM Subject: index on temporary table [4539] > We are using indexes on temporary tables, in programs written in Informix 4gl. > After migration from Informix SE 7.31 to Informix IDS 9.40HC6, we notice ( by set explain ) that the temp tables are readed in sequential mode, instead of using index ; > So the same program spent 20 mn on SE and 4 hours on IDS. > Is there some parameters to change ? > Thanks in advance. > > >

Glossary