Re: Optimizer of Online 7.22 selects wrong index
Dear Helmut,
Was this database ever on version 5.01x and then upgraded? If so you
may have the old npused bug still evident in that table. If you look
in systables after the update statistics and see npused < 0 then you
have this problem. The solution is to unload/drop/recreate the table.
Then the problem will not occur.
Otherwise use a stored procedure to recalculate the npused after
update statistics is run.
Regards,
Jason
On Wed, 30 Jul 1997 14:26:07 +0200, Helmut Leininger
<Helmut.Leininger@bull.net> wrote:
>
>--------------5A1C91DF9FE1AC5087D28943
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>I am running Informix Online 7.22 on Bull Escala (=J30 or J40). In some
>cases the optimizers chooses a wrong index for a often used query query.
>Here is one case:
>
>My table MSTUECK has about 745000 rows and several indexes, some of them
>having the first two or three columns in common. My query (WHERE columns
>match ORDER BY columns and the columns of one index) runs ok as long as
>I do not run an UPDATE STATISTICS against this table. It delivers about
>30 rows within 0.x seconds.
>After the execution af an UPDATE STATISTICS (whatever kind, with or
>without specifying columns) the optimizer get confused and makes a
>SEQUENTIAL SCAN followed by a SORT. Now the same query (with the same
>result) takes more than 5 minutes.
>Playing with OPTCOMPIND does not change the behaviour. Also, I cannot
>drop some indexes as they reflect the selection criteria/order criteria
>of other important queries. Up to now, my only bypass is to avoid
>running an UPDATE STATISTICS against the table (also a general UPDATE
>STATISTICS without a table specification must not be run).
>
>Does anyone have an idea?
>
>#################
>Here are the index definitions:
>
>#
>create unique index c_MSTUECK on MSTUECK (db__seq asc );
>create unique index MSTUECK on MSTUECK (
> st_firma asc ,
> st_snr asc ,
> st_stufe asc ,
> st_lfd asc ,
> st_posnr asc )> ;
>#
>create unique index MSTUECK2 on MSTUECK (
> st_firma asc ,
> st_zeichnr asc ,
> st_snr asc ,
> st_stufe asc ,
> st_lfd asc ,
> st_posnr asc , db__seq asc )> ;
>#
>create unique index MSTUECK3 on MSTUECK (
> st_firma asc ,
> st_tnr asc ,
> st_snr asc ,
> st_stufe asc ,
> st_lfd asc ,
> st_posnr asc , db__seq asc )> ;
>#
>create unique index MSTUECK4 on MSTUECK (
> st_firma asc ,
> st_matnr asc ,
> st_snr asc ,
> st_stufe asc ,
> st_lfd asc ,
> st_posnr asc , db__seq asc )> ;
>#
>create unique index MSTUECK5 on MSTUECK (
> st_firma asc ,
> st_snr asc ,
> st_stufe asc ,
> st_lfd asc ,
> st_kost asc ,
> st_lgort asc ,
> st_posnr asc , db__seq asc )> ;
>#
>create unique index MSTUECK6 on MSTUECK (
> st_firma asc ,
> st_snr asc ,
> st_lnr asc ,
> st_tg asc ,
> st_zeichnr asc ,
> st_stufe asc ,
> st_lfd asc ,
> st_posnr asc , db__seq asc )> ;
>#
>create unique index MSTUECK8 on MSTUECK (
> st_firma asc ,
> st_snr asc ,
> st_kost asc ,
> st_lgort asc ,
> st_stufe asc ,
> st_lfd asc ,
> st_posnr asc )> ;
>
>
>#######################
>This is the query:
>
>set explain on;
>SELECT st_firma , st_snr , st_stufe , st_lfd , st_posnr , st_stufeh ,
> st_lfdh , st_posnrh , st_stufea , st_lfda , st_reserviert ,
> st_zeichnr , st_tnr , st_menge , st_rmenge , st_restmenge , st_vdatum
>,
> st_matnr , st_bem , st_kost , st_lgort , st_kopte , st_lnr , st_tg ,
> st_status , st_mengepe , db__seq FROM MSTUECK
> WHERE st_firma = 1 AND st_zeichnr = "A113" AND st_snr > 1223
> ORDER BY st_firma ASC ,st_zeichnr ASC ,st_snr ASC ,st_stufe ASC ,
> st_lfd ASC ,st_posnr ASC ,db__seq ASC;
>set explain off;>
>
>#########################
>This is the result of the optimizer:
>
>====> Table loaded, no update statistics executed
>
>Beginn: Thu Jun 5 10:28:48 MEST 1997
>
>
>QUERY:
>------
>SELECT st_firma , st_snr , st_stufe , st_lfd , st_posnr , st_stufeh ,
> st_lfdh , st_posnrh , st_stufea , st_lfda , st_reserviert ,
> st_zeichnr , st_tnr , st_menge , st_rmenge , st_restmenge , st_vdatum
>,
> st_matnr , st_bem , st_kost , st_lgort , st_kopte , st_lnr , st_tg ,
> st_status , st_mengepe , db__seq FROM MSTUECK
> WHERE st_firma = 1 AND st_zeichnr = "A113" AND st_snr > 1223
> ORDER BY st_firma ASC ,st_zeichnr ASC ,st_snr ASC ,st_stufe ASC ,
> st_lfd ASC ,st_posnr ASC ,db__seq ASC>
>Estimated Cost: 1
>Estimated # of Rows Returned: 1
>
>1) lein.mstueck: INDEX PATH
>
> (1) Index Keys: st_firma st_zeichnr st_snr st_stufe st_lfd st_posnr
>db__seq
> Lower Index Filter: (lein.mstueck.st_firma = 1.0000000000000000
>AND (lein.mstueck.st_zeichnr = 'A113' AND lein.mstueck.st_snr >
>1223.0000000000000000 ) )
>
>Ende: Thu Jun 5 10:28:51 MEST 1997
>
>====> UPDATE STATISTICS executed
>
>Beginn: Thu Jun 5 10:35:40 MEST 1997
>
>
>QUERY:
>------
>SELECT st_firma , st_snr , st_stufe , st_lfd , st_posnr , st_stufeh ,
> st_lfdh , st_posnrh , st_stufea , st_lfda , st_reserviert ,
> st_zeichnr , st_tnr , st_menge , st_rmenge , st_restmenge , st_vdatum
>,
> st_matnr , st_bem , st_kost , st_lgort , st_kopte , st_lnr , st_tg ,
> st_status , st_mengepe , db__seq FROM MSTUECK
> WHERE st_firma = 1 AND st_zeichnr = "A113" AND st_snr > 1223
> ORDER BY st_firma ASC ,st_zeichnr ASC ,st_snr ASC ,st_stufe ASC ,
> st_lfd ASC ,st_posnr ASC ,db__seq ASC>
>Estimated Cost: 64797
>Estimated # of Rows Returned: 99318
>Temporary Files Required For: Order By
>
>1) lein.mstueck: INDEX PATH
>
> Filters: lein.mstueck.st_zeichnr = 'A113'
>
> (1) Index Keys: st_firma st_snr st_stufe st_lfd st_posnr
> Lower Index Filter: (lein.mstueck.st_firma = 1.0000000000000000
>AND lein.mstueck.st_snr > 1223.0000000000000000 )
>
>Ende: Thu Jun 5 10:40:46 MEST 1997
>
>
>
>#######
>
>Thanks for help
>
>
>--------------5A1C91DF9FE1AC5087D28943
>Content-Type: text/html; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
><HTML>
>I am running Informix Online 7.22 on Bull Escala (=J30 or J40). In some
>cases the optimizers chooses a wrong index for a often used query query.
>Here is one case:
>
><P>My table MSTUECK has about 745000 rows and several indexes, some of
>them having the first two or three columns in common. My query (WHERE columns
>match ORDER BY columns and the columns of one index) runs ok as long as
>I do not run an UPDATE STATISTICS against this table. It delivers about
>30 rows within 0.x seconds.
><BR>After the execution af an UPDATE STATISTICS (whatever kind, with or
>without specifying columns) the optimizer get confused and makes a SEQUENTIAL
>SCAN followed by a SORT. Now the same query (with the same result) takes
>more than 5 minutes.
><BR>Playing with OPTCOMPIND does not change the behaviour. Also, I cannot
>drop some indexes as the