IC83944: OPTIMIZER CHOOSES SEQUENTIAL SCAN WITH HIGH COST FOR A QUERY AGAINST 11.70.XC5. THIS BEHAVIOR IS DIFFERENT FROM 11.50.XC6 AND 9.

APAR status

• Closed as program error.

Error description

• Optimizer chooses sequential scan for a query in 11.70 but
  chooses INDEX PATH in 9.40 and 11.50
  
  QUERY: (OPTIMIZATION TIMESTAMP: 06-01-2012 18:49:12)
  ------
  select customer.SLMN_NUM, inv.CHARGE_TO, inv.CUST_NUM,
  customer.CUST_NAME,
      inv.TRANS_DATE, inv.TRANS_TYPE, case  when
  inv_shp_l.ITEM_NUM like
      '%FF8351%' then 3 else 5 end , inv_shp_l.COMMABLE_AMT,
      inv_shp_l.TOT_NET_AMT, inv_shp_l.WHSE_CODE,
  inv_shp_l.ITEM_NUM,
      item_.DESC_1, item_.SA_ITEM, inv_shp.ORDER_TYPE_CODE,
  inv.TRANS_NUM,
      inv_shp_l.TOT_NET_AMT - inv_shp_l.COMMABLE_AMT, case  when
      inv_shp.ORDER_TYPE_CODE='CONV' then .02 when
      inv_shp.ORDER_TYPE_CODE<>'CONV' and inv_shp_l.WHSE_CODE in
  ('VHDI',
      'WBDI', 'ACDI', 'CHINA', 'INDIA') then .03 when
      inv_shp.ORDER_TYPE_CODE<>'CONV' and inv_shp_l.WHSE_CODE not
  in ('VHDI',
      'WBDI', 'ACDI', 'CHINA', 'INDIA') then .05 else 0 end ,
      inv_shp_l.COMMABLE_AMT*case  when
  (inv_shp.ORDER_TYPE_CODE='CONV') then
      .02 when (inv_shp.ORDER_TYPE_CODE<>'CONV' and
  inv_shp_l.WHSE_CODE in
      ('VHDI', 'WBDI', 'ACDI', 'CHINA', 'INDIA')) then .03 when
      (inv_shp.ORDER_TYPE_CODE<>'CONV' and inv_shp_l.WHSE_CODE not
  in ('VHDI',
      'WBDI', 'ACDI', 'CHINA', 'INDIA')) then .05 else 0 end ,
  inv.ORG_CODE,
      inv_shp_l.SLMN_NUM, case  when inv_shp_l.ITEM_NUM like
  '%FF8351%' then 3
      else 5 end /100, inv_shp.CUST_PO_NUM from ((CUSTOMER
  customer LEFT OUTER
      JOIN INV inv on customer.CUST_NUM=inv.CUST_NUM) LEFT OUTER
  JOIN INV_SHP
      inv_shp on inv.INV_ID=inv_shp.INV_ID) LEFT OUTER JOIN (ITEM
  item_ inner
      join INV_SHP_L inv_shp_l on
  item_.ITEM_NUM=inv_shp_l.ITEM_NUM) on
      inv_shp.INV_SHP_ID=inv_shp_l.INV_SHP_ID where
  inv.TRANS_DATE>=DATETIME(
      2012-04-26 00:00:00.00000 ) YEAR TO FRACTION(5) and
      inv.TRANS_DATE<=DATETIME( 2012-05-23 23:59:59.99900 ) YEAR
  TO FRACTION(5)
      and inv_shp_l.SLMN_NUM='C001' and item_.ITEM_GROUP in
  ('SENS', 'COSH')
      order by 20 asc
  
  Estimated Cost: 337408832
  Estimated # of Rows Returned: 21
  Temporary Files Required For: Order By
  
            1) informix.item_: SEQUENTIAL SCAN
  
                  Filters: informix.item_.item_group IN ('SENS' ,
  'COSH' )
  
            2) informix.inv_shp_l: INDEX PATH
  
                  Filters: informix.inv_shp_l.slmn_num = 'C001'
  
              (1) Index Name: tecsys.inv_shp_l2
                  Index Keys: item_num whse_code   (Serial,
  fragments: ALL)
                  Lower Index Filter: informix.item_.item_num =
  informix.inv_shp_l.item_num
  
          ON-Filters:informix.item_.item_num =
  informix.inv_shp_l.item_num
          NESTED LOOP JOIN
  
                3) informix.inv: INDEX PATH
                 (1) Index Name: tecsys.inv6
                      Index Keys: trans_date   (Serial, fragments:
  ALL)
                      Lower Index Filter: informix.inv.trans_date
  >= 2012-04-26
                      Upper Index Filter: informix.inv.trans_date
  <= 2012-05-23
  
                4) informix.customer: INDEX PATH
  
                  (1) Index Name: tecsys.customer
                      Index Keys: cust_num   (Serial, fragments:
  ALL)
                      Lower Index Filter:
  informix.customer.cust_num = informix.inv.cust_num
  
              ON-Filters:informix.customer.cust_num =
  informix.inv.cust_num
              NESTED LOOP JOIN
  
            5) informix.inv_shp: INDEX PATH
  
              (1) Index Name: tecsys.inv_shp
                  Index Keys: inv_shp_id   (Serial, fragments:
  ALL)
                  Lower Index Filter: informix.inv_shp.inv_shp_id
  = informix.inv_shp_l.inv_shp_id
  
          ON-Filters:informix.inv.inv_id = informix.inv_shp.inv_id
          NESTED LOOP JOIN(LEFT OUTER JOIN)
  
      ON-Filters:informix.inv_shp.inv_shp_id =
  informix.inv_shp_l.inv_shp_id
      NESTED LOOP JOIN
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of ANSI queries                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IDS-11.70.xC6                                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IDS-11.70.xC6
  

Temporary fix

Comments

• http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp
  http://www.ibm.com/support/fixcentral
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels