Re: Serious Flaws in 7.23's Optimizer - Not using indexes
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--
>