SET EXPLAIN ON/Indexing strategies

Posted in 1999

Topics: Performance & Tuning, SQL Development & Query Writing

Graeme Muirhead — Wed, 21 Jul 1999 09:35:43 GMT — source: Informix-list mailing list archive (1991-1998)

I have a performance problem running a batch job - Informix Online on HPUX 11. I have run the SQL with SET EXPLAIN ON and the sqexplain.out file show a cost of over 2000. I have tried putting a duplicate index on the one unindexed field in the select statement without any noticeable improvement. Can anyone advise on alternative indexing techniques e.g. would a composite index on all the fields selected improve? Thanks Graeme Muirhead g_muirhead@csisystems.co.uk

↪ replying to Graeme Muirhead

Yuri Dovgart — Wed, 21 Jul 1999 11:16:24 GMT — source: Informix-list mailing list archive (1991-1998)

In article <Pvgl3.5248$ts3.143717@nnrp4.clara.net>, "Graeme Muirhead" <csi@muirhead.freeuk.com> wrote: > I have a performance problem running a batch job - Informix Online on HPUX > 11. I have run the SQL with SET EXPLAIN ON and the sqexplain.out file show a > cost of over 2000. I have tried putting a duplicate index on the one > unindexed field in the select statement without any noticeable improvement. > Can anyone advise on alternative indexing techniques e.g. would a composite > index on all the fields selected improve? First of all, before creating index, you need select right columns for creation. For example, there is no reason to create index for specified field, if this field is not unique enough. I mean, that before you create an index, you need analyse your data and select column, or combination of columns, that have as much unique combinations as possible. Ideal case is unique index. After creating an index, you need to UPDATE STATISTICS. -- With best regards, Yuri Dovgart, SAP R/3, Informix consultant, "Telecominvest" company. E-mail y_dovgart@tci.ukrtel.net Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

↪ replying to Graeme Muirhead

David Williams — Wed, 21 Jul 1999 13:08:29 +0100 — source: Informix-list mailing list archive (1991-1998)

In article <Pvgl3.5248$ts3.143717@nnrp4.clara.net>, Graeme Muirhead <csi@muirhead.freeuk.com> writes >I have a performance problem running a batch job - Informix Online on HPUX >11. I have run the SQL with SET EXPLAIN ON and the sqexplain.out file show a >cost of over 2000. I have tried putting a duplicate index on the one >unindexed field in the select statement without any noticeable improvement. >Can anyone advise on alternative indexing techniques e.g. would a composite >index on all the fields selected improve? > >Thanks > We need the sqexplain.out file and table schemas to be able to help.. >Graeme Muirhead >g_muirhead@csisystems.co.uk > > -- David Williams