Re[2]: Serious Flaws in 7.23's Optimizer - Not using indexes
Thanks to everyone who has been trying to help us with this problem.
Alan's post was misleading, if not inaccurate.
The steps outlined below are exactly what we are doing. And I have tested
the sql right after running the correct set of update statistics. From
looking at the sql, I believe we have just hit upon the problem with the OR
clause forcing us to use UNION. This was mentioned in several other emails
in the last couple of weeks. Someone said that the OR's used to work in
version 4 of ISQL, but not in version 6. Does anyone know if Informix
considers this a bug or if we just have to use UNIONS now?
Thanks, Dianne
______________________________ Reply Separator _________________________________
Subject: Re: Serious Flaws in 7.23's Optimizer - Not using indexes
Author: dpohlman@fuse.net (Dan Pohlman) at INTERNET
Date: 8/7/97 4:08 PM
Actually, you should check out the Informix SQL Syntax guide for the
correct way to run update statistics. This varies between 7.10.UC1,
7.10.UD1, 7.11.UC1 and 7.2x. For 7.23 it should be on page 1-631.
In looking at this the correct steps are:
update statistics medium for table table_name distributions only; (note information in regard to large tables)
update statistics high for columns which head indexes
(note there should be one SQL statement per head column)
( also keep in mind that this can be done with )
( distributions only since the next step takes care )
( of nrows, etc.)
update statistics low for all columns in the index
( note this is a very important step and if not done properly)
( incorrect paths can be taken. )
Dan Pohlman
Helmut Leininger <Helmut.Leininger@bull.net> wrote:
}
}--------------B3C27D3E84BDDA7B2705D81B
}Content-Type: text/plain; charset=us-ascii
}Content-Transfer-Encoding: 7bit
}
}Paul Mosser schrieb:
}
}> Try setting OPTCOMPIND (in the onconfig file) to 0, instead of the
}> default 2.
}>
}> --
}> ==============================
}> pmosser@minisoftinc.com
}> Paul A. Mosser, Developer/DBA
}> MiniSoft, Inc. Phoenix, AZ U.S.A.
}>
}> } -----Original Message-----
}> } From: alan.cowan@autodesk.com [SMTP:alan.cowan@autodesk.com]
}> } Sent: Wednesday, August 06, 1997 9:38 AM
}> } To: informix-list@rmy.emory.edu; Alastair Kimbell
}> } Subject: Serious Flaws in 7.23's Optimizer - Not using indexes
}> }
}> } In a nutshell:
}> } The Optimizer does not usee indexes even if EVERY join is an
}>
}> } indexed
}> } field.
}> } Lots of problems in other examples including every time I
}> use
}> } EXISTS it
}> } does a SEQUENTIAL search where an index is available.
}> } Informix 5 did not have these problems.
}> }
}> } [snip]
}> }
}> } Wish I were back with v5
}> }
}> } Alan Cowan
}
}It seems there are some things which upset the optimizer. If playing
}with OPTCOMPIND does not help, try the following
} UPDATE STATISTICS MEDIUM FOR table
} UPDATE STATISTICS HIGH FOR table (keyfield1)
} UPDATE STATISTICS HIGH FOR table (keyfield2)
}
} .....
}
}Good luck
}Helmut
}
}
}--------------B3C27D3E84BDDA7B2705D81B
}Content-Type: text/html; charset=us-ascii
}Content-Transfer-Encoding: 7bit
}
}<HTML>
}Paul Mosser schrieb:
}<BLOCKQUOTE TYPE=CITE>Try setting OPTCOMPIND (in the onconfig file) to
}0, instead of the
}<BR>default 2.
}
}<P>--
}<BR>==============================
}<BR>pmosser@minisoftinc.com
}<BR>Paul A. Mosser, Developer/DBA
}<BR>MiniSoft, Inc. Phoenix, AZ U.S.A.
}
}<P>} -----Original Message-----
}<BR>} From: alan.cowan@autodesk.com [SMTP:alan.cowan@autodesk.com]
}<BR>} Sent: Wednesday, August 06, 1997 9:38 AM
}<BR>} To: informix-list@rmy.emory.edu; Alastair Kimbell
}<BR>} Subject: Serious Flaws in 7.23's Optimizer
}- Not using indexes
}<BR>}
}<BR>} In a nutshell:
}<BR>} The Optimizer does
}not usee indexes even if EVERY join is an
}<BR>} indexed
}<BR>} field.
}<BR>} Lots of problems
}in other examples including every time I use
}<BR>} EXISTS it
}<BR>} does a SEQUENTIAL
}search where an index is available.
}<BR>} Informix 5 did not
}have these problems.
}<BR>}
}<BR>} [snip]
}<BR>}
}<BR>} Wish I were back with v5
}<BR>}
}<BR>} Alan Cowan</BLOCKQUOTE>
}It seems there are some things which upset the optimizer. If playing with
}OPTCOMPIND does not help, try the following
}<BR> UPDATE STATISTICS MEDIUM FOR table
}<BR> UPDATE STATISTICS HIGH FOR table (keyfield1)
}<BR> UPDATE STATISTICS HIGH FOR table (keyfield2)
}
}<P> .....
}
}<P>Good luck
}<BR>Helmut
}<BR> </HTML>
}
}--------------B3C27D3E84BDDA7B2705D81B--
}