|
Problem IT26368
|
Status: Closed |
HASH JOIN BETWEEN TABLES WITH ADDITIONAL LIKE FILTER WITH WILDCARDS USING MUCH MORE CPU WITH EN_US.UTF8 THAN EN_US.819
|
| product: |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* 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: *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.09.2018
26.09.2019
26.09.2019 |
| Problem solved at the following versions (IBM BugInfos) |
12.10.xC13
|
| Problem solved according to the fixlist(s) of the following version(s) |
