Informix - Problem description
Problem IT09504 | Status: Closed |
POOR PERFORMANCE IF SIMPLE- AND ANSI-JOINS COMBINED | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
Running a SELECT statement that contains the syntax for both joins methods results in non optimal access and subsequently in a slow query performance. If no "UPDATE STATISTICS MEDIUM | HIGH" or "UPDATE STATISTICS LOW" where done the result-set is returned fast. For below statement the sqexplain will show a huge amount of rows_prod for table "tab3" and "tab4". Rewriting the query and moving (e. g.) the LEFT JOIN in the WHERE clause gives acceptable performance. # with LEFT JOIN type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- scan t3 134359096 19952 134359096 02:55.23 1 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- scan t4 268718192 46762 134359096 19:25.54 32 # LEFT JOIN moved in the WHERE clause type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- scan t3 20102 8513 20102 00:00.13 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 20102 1 00:00.18 3 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- scan t4 20102 8513 20102 00:00.17 1 # Statement (before re-writing) SELECT tab1.col2, tab3.col3, tab4.col4, tab2.col3 FROM tab1, tab2, tab3 LEFT JOIN tab4 d ON ( tab4.col1 = tab3.col1 AND tab4.col2 = tab3.col2 AND tab4.col3 = tab3.col3 AND tab4.col4 = 'text' ) WHERE tab1.col1 = 1 AND tab1.col2 LIKE '2-TX%' AND tab2.col1 = 1 AND tab2.col2 = tab1.col2 AND tab3.col1 = 1 AND tab3.col2 = tab1.col2 AND tab3.col3 = tab2.col3; | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of 12.10.xC1 through 12.10.xC6 * **************************************************************** * PROBLEM DESCRIPTION: * * A query with mixed ANSI JOIN and Informix join syntax may * * have sub-optimal query plan under the following conditions: * * - query has both ANSI JOIN and Informix join syntax mixed, * * e.g. FROM tab1, tab2, tab3 LEFT JOIN tab4 on ... * * - tab3 which is the dominant table in the ANSI JOIN * * syntax, has both join predicates with tab1 and tab2, on the * * same column * * e.g., tab3.col3 = tab1.col1 AND tab3.col3 = tab2.col2 * * - there exists at least two indexes on tab3, one with col3 * * as leading column, the other is a composite index with * * multiple index keys including col3 * * e.g. tab3_idx1(col3), tab3_idx2(col2, col3) * * - both tab2.col2 and tab3.col3 have at least medium * * statistics, with both having significant overflow bins and * * the values of overflow bins matches * * between tab2.col2 and tab3.col3. * * - tab1.col1 does not have the same matching overflow bins * * as tab2.col2 or tab3.col3 * **************************************************************** * RECOMMENDATION: * * Update to IBM Informix Server 12.10.xC7 * **************************************************************** | |
Local Fix: | |
Work around: Rewriting the query and moving (e. g.) the LEFT JOIN in the WHERE clause. | |
Solution | |
Problem Fixed In IBM Informix Server 12.10.xC7 | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 17.06.2015 28.06.2016 28.06.2016 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC7 | |
Problem solved according to the fixlist(s) of the following version(s) |