INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

From the set explain output (in the statistics section) consider
the following hash join that's occuring:

  3) (Temp Table For Collection Subquery): SEQUENTIAL SCAN
NESTED LOOP JOIN

  4) owner.gl_budget_0_normalised: INDEX PATH

    (1) Index Name: owner.genbudper7b
        Index Keys: budget_no glbp_gl_accountcode glbp_period
(Serial, fragments: ALL)
        Lower Index Filter:
owner.gl_budget_0_normalised.budget_no = '0'


DYNAMIC HASH JOIN (Build Outer)
    Dynamic Hash Filters: (Temp Table For Collection
Subquery).sctd_period =
owner.gl_budget_0_normalised.glbp_period

    Other Join Filters:
owner.gl_budget_0_normalised.glbp_gl_accountcode LIKE
owner.glc_hierarchy_master_m.account_pattern

now the statistics pieces for that join (using UTF8)

Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                t1
  t2                glc_hierarchy_master_m
  t3                sctd_gl_dmr
  t4                (Temp Table For Collection Subquery)
  t5                gl_budget_0_normalised
...
  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t4     46488      8         46488      00:00.02   2

  type     rows_prod  est_rows  time       est_cost
  -------------------------------------------------
  nljoin   46488      4813      00:00.04   3054

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t5     11268      11268     11268      00:00.10
1068

  type     rows_prod  est_rows  rows_bld  rows_prb  novrflo
time       est_cost

----------------------------------------------------------------
--------------
  hjoin    10704      180771    46488     11268     0
01:50.73   7163

so the hash join consumes ~110 seconds in this case.

If we switch to en_us.819 the same hash join uses much less cpu

from explain output (using en_us.819)

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                t1
  t2                glc_hierarchy_master_m
  t3                sctd_gl_dmr
  t4                (Temp Table For Collection Subquery)
  t5                gl_budget_0_normalised
  t6                gl_master79
  t7                genusecdet0
...
  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t4     46488      8         46488      00:00.01   2

  type     rows_prod  est_rows  time       est_cost
  -------------------------------------------------
  nljoin   46488      4813      00:00.04   3054

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t5     11268      11268     11268      00:00.08
1068

  type     rows_prod  est_rows  rows_bld  rows_prb  novrflo
time       est_cost

----------------------------------------------------------------
--------------
  hjoin    10704      180771    46488     11268     0
00:08.22   7163

with en_us.819 it ok requires ~8 seconds of cpu.

****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS prior to 12.10.xC13.                            *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* Hash join between tables with additional filter using LIKE   *
* and wildcards uses much more cpu using en_us.UTF8 compared   *
* to en_us.819.                                                *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

20.09.2018
26.09.2019
26.09.2019

12.10.xC13