Informix - Problem description
Problem IT34482 | Status: Closed |
DIFFERENT QUERY PLAN (PERFORMANCE) FOR SAME SQL INSIDE SPL | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
QUERY: (OPTIMIZATION TIMESTAMP: 09-19-2020 13:32:22) ------ SELECT p.pcol1, p.pcol3, p.pcol4, p.pcol5, p.pcol6, pr.prcol3, pr.prcol4, c.ccol6, c.ccol5, c.ccol1, c.ccol7, c.ccol8, c.ccol9, c.ccol4, c.ccol2, p.pcol2 FROM tblp p, tblpr pr, tblc c, tblb b WHERE b.bcol2 = 61 AND c.ccol5 BETWEEN 53042990 AND 53042990 AND c.ccol6 = b.bcol1 AND p.pcol2 = c.ccol3 AND p.pcol7 = "Y" AND pr.prcol1 = c.ccol1 AND pr.prcol2 = c.ccol4 ORDER BY pr.prcol3 desc Estimated Cost: 7 Estimated # of Rows Returned: 1 Temporary Files Required For: Order By 1) informix.c: INDEX PATH (1) Index Name: informix.idx1tblc Index Keys: ccol5 (Serial, fragments: ALL) Lower Index Filter: informix.c.ccol5 = 53042990 2) informix.pr: INDEX PATH Filters: informix.pr.prcol2 = informix.c.ccol4 (1) Index Name: informix.idxtblpr Index Keys: prcol1 (Serial, fragments: ALL) Lower Index Filter: informix.pr.prcol1 = informix.c.ccol1 NESTED LOOP JOIN 3) informix.p: INDEX PATH Filters: informix.p.pcol7 = 'Y' (1) Index Name: informix.uidxtblp Index Keys: pcol2 pcol1 (Serial, fragments: ALL) Lower Index Filter: informix.p.pcol2 = informix.c.ccol3 NESTED LOOP JOIN 4) informix.b: INDEX PATH (1) Index Name: informix.idx2tblb Index Keys: bcol1 bcol2 (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.c.ccol6 = informix.b.bcol1 Index Key Filters: (informix.b.bcol2 = 61 ) NESTED LOOP JOIN Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 c t2 pr t3 p t4 b type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 1 1 1 00:00.00 1 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t2 1 1000 1 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 1 1 00:00.00 3 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t3 1 100 1 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 1 1 00:00.00 6 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t4 0 25 1 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:00.00 7 type rows_sort est_rows rows_cons time est_cost ------------------------------------------------------------ sort 0 1 0 00:00.00 0 Against the SPL: ---------- Procedure: informix.sp_version_ibm Statement id: 29 select x0.pcol1 ,x0.pcol3 ,x0.pcol4 ,x0.pcol5 ,x0.pcol6 ,x1.prcol3 ,x1.prcol4 ,x3.gccol6 ,x3.gccol7 ,x3.gccol3 ,x3.gccol10 ,x3.gccol9 ,x3.gccol8 ,x1.prcol2 ,x3.gccol4 ,x0.pcol2 ,x3.gccol2 ,x3.gccol5 from "informix".tblp x0 ,"informix".tblpr x1 ,"informix".tbld x2 ,"informix".tblgc x3 ,"informix".tblg x4 ,"informix".tblb x5 where (((((((((((x5.bcol2 = ? ) AND ((x3.gccol7 >= ? ) AND (x3.gccol7 = '' Upper Index Filter: informix.gc.gccol7 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users having 12.10.xC10 or prior version * **************************************************************** * PROBLEM DESCRIPTION: * * Different Query Plan (performance) for same SQL inside SPL * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users having 12.10.xC10 or prior version * **************************************************************** * PROBLEM DESCRIPTION: * * Different Query Plan (performance) for same SQL inside SPL * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Comment | |
The issue is not fixable, but user can have another option to use dynamic sql statement. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 08.10.2020 02.02.2023 02.02.2023 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |