INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

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

****************************************************************
* USERS AFFECTED:                                              *
* Users having 12.10.xC10 or prior version                     *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* Different Query Plan (performance) for same SQL inside SPL   *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

****************************************************************
* USERS AFFECTED:                                              *
* Users having 12.10.xC10 or prior version                     *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* Different Query Plan (performance) for same SQL inside SPL   *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

The issue is not fixable, but user can have another option to
use dynamic sql statement.

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

08.10.2020
02.02.2023
02.02.2023