INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

The following problem was reported on a special build of
12.10.FC7W1 on Linux.  I confirmed the  problem
reproduces on 12.10.FC12W1 as well.

The customer reported a query that is run on several servers.
On some of these servers the optimizer is
generating a plan with a lower estimated cost that takes longer
to run than the alternative plan that the
optimizer is generating on other servers.  The alternate
desirable plan can be generated on the servers
chasing the slow plan with the ORDERED optimizer directive which
reports a higher estimated cost than
the slow plan.

The query:

SELECT  NVL(SUM((su.ship_unit_qty*i.whpk_qty/i.vnpk_qty)
                  *dit.vnpk_weight_qty),0)
      , NVL(SUM((su.ship_unit_qty*i.whpk_qty/i.vnpk_qty)*
                 (dit.vnpk_length_qty*dit.vnpk_width_qty*
                 dit.vnpk_height_qty)),0)
      , NVL(SUM(SU.ship_unit_qty*i.whpk_qty/i.vnpk_qty),0)
      , NVL(SUM(SU.ship_unit_qty) ,0)
      , L.ship_door_nbr
      , L.ship_subcenter_nbr
FROM
     dc_sys_order_prcss:Load L,
OUTER  ( dc_sys_order_prcss:shipment SH ,
     dc_sys_order_prcss: Shipping_Unit SU,
     dc_sys_receiving:receiving_unit  RU ,
     dc_sys_common:Item I ,
     dc_sys_common:dc_item dit )
WHERE
L.load_id = SH.load_id
AND   SH.shipment_id = SU.shipment_id
AND   SU.ship_unit_stat_cd IN (20 , 21 )
AND   SU.rcv_unit_id = RU.rcv_unit_id
AND   RU.item_nbr = I.item_nbr
AND   RU.purch_company_id = I.purch_company_id
AND   I.item_nbr = dit.item_nbr
AND I.purch_company_id = dit.purch_company_id
AND L.load_id = 27926
AND SH.stop_status_code != 3
GROUP BY  L.ship_door_nbr ,
          L.ship_subcenter_nbr

The slow plan:

Estimated Cost: 2589
Estimated # of Rows Returned: 36
Temporary Files Required For: Group By

  1) informix.l: INDEX PATH

    (1) Index Name: informix.load_p01_expl
        Index Keys: load_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.l.load_id = 27926

  2) informix.sh: INDEX PATH

        Filters: informix.sh.stop_status_code != 3

    (1) Index Name: informix.shipment_f02_expl
        Index Keys: load_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.l.load_id =
informix.sh.load_id
NESTED LOOP JOIN

  3) informix.dit: SEQUENTIAL SCAN
NESTED LOOP JOIN

  4) informix.i: INDEX PATH

    (1) Index Name: informix.item_p01_expl
        Index Keys: item_nbr purch_company_id   (Serial,
fragments: ALL)
        Lower Index Filter: (informix.i.item_nbr =
informix.dit.item_nbr AND informix.i.purch_company_id =
informix.dit.purch_company_id )
NESTED LOOP JOIN

  5) informix.ru: INDEX PATH

    (1) Index Name: informix.receiving_unit_i03
        Index Keys: item_nbr purch_company_id rcv_unit_stat_code
curr_unit_qty   (Serial, fragments: ALL)
        Lower Index Filter: (informix.ru.item_nbr =
informix.i.item_nbr AND informix.ru.purch_company_id =
informix.i.purch_company_id )
NESTED LOOP JOIN

  6) informix.su: INDEX PATH

        Filters: (informix.sh.shipment_id =
informix.su.shipment_id AND informix.su.ship_unit_stat_cd IN (20
, 21 ))

    (1) Index Name: informix.shipping_unit_ix01
        Index Keys: rcv_unit_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.su.rcv_unit_id =
informix.ru.rcv_unit_id
NESTED LOOP JOIN

The desirable plan
********************

DIRECTIVES FOLLOWED:
ORDERED
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 4345
Estimated # of Rows Returned: 36
Temporary Files Required For: Group By

  1) informix.l: INDEX PATH

    (1) Index Name: informix.load_p01_expl
        Index Keys: load_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.l.load_id = 27926

  2) informix.sh: INDEX PATH

        Filters: informix.sh.stop_status_code != 3

    (1) Index Name: informix.shipment_f02_expl
        Index Keys: load_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.l.load_id =
informix.sh.load_id
NESTED LOOP JOIN

  3) informix.su: INDEX PATH

        Filters: informix.su.ship_unit_stat_cd IN (20 , 21 )

    (1) Index Name: informix.shipping_unit_i01
        Index Keys: shipment_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.sh.shipment_id =
informix.su.shipment_id
NESTED LOOP JOIN

  4) informix.ru: INDEX PATH

    (1) Index Name: informix.receiving_unit_p01_expl
        Index Keys: rcv_unit_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.su.rcv_unit_id =
informix.ru.rcv_unit_id
NESTED LOOP JOIN

  5) informix.i: INDEX PATH

    (1) Index Name: informix.item_p01_expl
        Index Keys: item_nbr purch_company_id   (Serial,
fragments: ALL)
        Lower Index Filter: (informix.ru.item_nbr =
informix.i.item_nbr AND informix.ru.purch_company_id =
informix.i.purch_company_id )
NESTED LOOP JOIN

  6) informix.dit: INDEX PATH

    (1) Index Name: informix.dc_item_p01_expl
        Index Keys: item_nbr purch_company_id   (Serial,
fragments: ALL)
        Lower Index Filter: (informix.i.item_nbr =
informix.dit.item_nbr AND informix.i.purch_company_id =
informix.dit.purch_company_id )
NESTED LOOP JOIN

****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS prior to 12.10.xC13.                            *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* Query runs faster using plan with a higher cost via ORDERED  *
* directive than chosen lower cost plan.                       *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

17.04.2019
24.09.2019
24.09.2019

12.10.xC13