Informix - Problem description
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) |