IT30688: 12.10 CHOOSES SEQUENTIAL SCAN OVER INDEX AS IN 11.70 IF DS_TOTAL_MEMORY / DS_MAX_QUERIES QUANTUM < 3700KB ON NON-PDQ QUERY

APAR status

• Closed as program error.

Error description

• See the sqexplain differences:
  
  12.10.FC12
  
  QUERY: (OPTIMIZATION TIMESTAMP: 06-11-2019 12:04:13)
  ------
  select first 501 distinct client0_.client_id as col_0_0_,
  client0_.first_name as col_1_0_,
  client0_.middle_name as col_2_0_, client0_.last_name as
  col_3_0_,
  client0_.generation as col_4_0_, client0_.date_of_birth as
  col_5_0_,
  client0_.fbi_no as col_6_0_, client0_.ssn as col_7_0_,
  client0_.regis_marsh_num as col_8_0_,
  client0_.ins_no as col_9_0_
  
  from client client0_
  inner join alternate_name alternaten1_ on
  client0_.client_id=alternaten1_.client_id
        and alternaten1_.lcd_id=client0_.lcd_id
  left outer join client_address clientaddr2_ on
  client0_.client_id=clientaddr2_.client_id
              and clientaddr2_.lcd_id=client0_.lcd_id
  
  where 1=1
    and (lower(client0_.last_name)=lower('Smith') or
  lower(alternaten1_.last_name)=lower('Smith'))
    and client0_.lcd_id= "0970"
  
  order by client0_.last_name, client0_.first_name,
  client0_.middle_name
  
  
  
  Estimated Cost: 1860568
  Estimated # of Rows Returned: 44582
  Temporary Files Required For: Order By
  
    1) informix.client0_: INDEX PATH (SKIP SCAN)
  
      (1) Index Name: informix. 167_4447
          Index Keys: lcd_id second_country_citizenship   (Serial,
  fragments: ALL)
          Lower Index Filter: informix.client0_.lcd_id = '0970'
  
    2) informix.alternaten1_: SEQUENTIAL SCAN
  
          Filters:
          Table Scan Filters: informix.alternaten1_.lcd_id =
  '0970'
  
  
  DYNAMIC HASH JOIN (Build Outer)
      Dynamic Hash Filters: informix.client0_.client_id =
  informix.alternaten1_.client_id
  
      Other Join Filters: (LOWER(informix.client0_.last_name ) =
  'smith' OR LOWER(informix.alternaten1_.last_name ) = 'smith' )
  
    3) informix.clientaddr2_: INDEX PATH
  
      (1) Index Name: informix. 531_4323
          Index Keys: lcd_id client_id   (Key-Only)  (Serial,
  fragments: ALL)
          Lower Index Filter: (informix.client0_.client_id =
  informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id
  = informix.client0_.lcd_id )
  NESTED LOOP JOIN
  
  11.70.FC8:
  
  QUERY: (OPTIMIZATION TIMESTAMP: 06-11-2019 12:36:46)
  ------
  select first 501 distinct client0_.client_id as col_0_0_,
  client0_.first_name as col_1_0_,
  client0_.middle_name as col_2_0_, client0_.last_name as
  col_3_0_,
  client0_.generation as col_4_0_, client0_.date_of_birth as
  col_5_0_,
  client0_.fbi_no as col_6_0_, client0_.ssn as col_7_0_,
  client0_.regis_marsh_num as col_8_0_,
  client0_.ins_no as col_9_0_
  
  from client client0_
  inner join alternate_name alternaten1_ on
  client0_.client_id=alternaten1_.client_id
        and alternaten1_.lcd_id=client0_.lcd_id
  left outer join client_address clientaddr2_ on
  client0_.client_id=clientaddr2_.client_id
              and clientaddr2_.lcd_id=client0_.lcd_id
  
  where 1=1
    and (lower(client0_.last_name)=lower('Smith') or
  lower(alternaten1_.last_name)=lower('Smith'))
    and client0_.lcd_id= "0970"
  
  order by client0_.last_name, client0_.first_name,
  client0_.middle_name
  
  
  
  Estimated Cost: 2007810
  Estimated # of Rows Returned: 47971
  Temporary Files Required For: Order By
  
            1) informix.client0_: INDEX PATH
  
              (1) Index Name: informix. 167_4447
                  Index Keys: lcd_id second_country_citizenship
  (Serial, fragments: ALL)
                  Lower Index Filter: informix.client0_.lcd_id =
  '0970'
  
            2) informix.alternaten1_: INDEX PATH
  
              (1) Index Name: informix.idx_altnnm_clntid
                  Index Keys: lcd_id client_id   (Key-First)
  (Serial, fragments: ALL)
                  Lower Index Filter: (informix.client0_.client_id
  = informix.alternaten1_.client_id AND
  informix.alternaten1_.lcd_id = informix.client0_.lcd_id )
                  Index Key Filters:
  (informix.alternaten1_.lcd_id = '0970' )
  
          ON-Filters:(informix.client0_.client_id =
  informix.alternaten1_.client_id AND informix.alternaten1_.lcd_id
  = informix.client0_.lcd_id )
          Other Join Filters: (LOWER(informix.client0_.last_name )
  = 'smith' OR LOWER(informix.alternaten1_.last_name ) = 'smith' )
          NESTED LOOP JOIN
  
        3) informix.clientaddr2_: INDEX PATH
  
          (1) Index Name: informix. 531_4323
              Index Keys: lcd_id client_id   (Key-Only)  (Serial,
  fragments: ALL)
              Lower Index Filter: (informix.client0_.client_id =
  informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id
  = informix.client0_.lcd_id )
  
      ON-Filters:(informix.client0_.client_id =
  informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id
  = informix.client0_.lcd_id )
      NESTED LOOP JOIN(LEFT OUTER JOIN)
  
  PostJoin-Filters:(LOWER(informix.client0_.last_name ) = 'smith'
  OR LOWER(informix.alternaten1_.last_name ) = 'smith' )
  
  
  Onconfig relevant parameters:
  
  MAX_PDQPRIORITY 0
  DS_MAX_QUERIES 100
  DS_TOTAL_MEMORY 7000000
  DS_MAX_SCANS  1048576
  DS_NONPDQ_QUERY_MEM 1500000
  DATASKIP    off
  
  If the DS_TOTAL_MEMORY / DS_MAX_QUERIES quantum ~ 3700kb then
  optimizer chooses the INDEX SCAN
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3.  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to Informix Server 12.10.xC14 or 14.10.xC3.           *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 12.10.xC14 and 14.10.xC3.
  

Temporary fix

Comments

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