INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

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;

**************************************************************** 
* 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                      * 
****************************************************************

Work around: 
Rewriting the query and moving (e. g.) the LEFT JOIN in the 
WHERE clause.

Problem Fixed In IBM Informix Server 12.10.xC7

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

17.06.2015
28.06.2016
28.06.2016

12.10.xC7