Re: Question about MIN/MAX optimization

> Hi.
>
> Try this. It will probably generate one indexs can instead of two.
>
> with temp (abc) as
> =A0 =A0 ( select abc from T were id =A0=3D ?)
> select =A0max (abc), min(abc) from temp
>
> /dg


DG, DB2 optimized the original statement into two index scans.

Original Statement:
------------------
with temp (COLLECT_TIME) as
       (select COLLECT_TIME
       from RTM.TBL_COLLECT_PSSTAT_WIN_RTM
       where MACHINE_ID=3D ? and COLLECT_TIME > ?)
select MIN(COLLECT_TIME), MAX(COLLECT_TIME)
from temp


Optimized Statement:
-------------------
SELECT Q6.$C0, Q3.$C0
FROM
   (SELECT MAX(Q2.$C0)
   FROM
      (SELECT Q1.COLLECT_TIME
      FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q1
      WHERE (:? < Q1.COLLECT_TIME) AND (Q1.MACHINE_ID =3D :?)) AS Q2) AS
Q3,
   (SELECT MIN(Q5.$C0)
   FROM
      (SELECT Q4.COLLECT_TIME
      FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q4
      WHERE (:? < Q4.COLLECT_TIME) AND (Q4.MACHINE_ID =3D :?)) AS Q5) AS
Q6

Access Plan:
-----------
        Total Cost:             51.2774
        Query Degree:           1

              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
                1
             NLJOIN
             (   2)
             51.2774
             7.9774
          /-----+-----\
        1                1
     GRPBY            GRPBY
     (   3)           (   5)
     25.6383          25.6383
     3.98869          3.98871
       |                |
     448.726          448.726
     IXSCAN           IXSCAN
     (   4)           (   6)
     87.5883          87.5879
     13.6267          13.6267
       |                |
   1.48278e+06      1.48278e+06
 INDEX: RTM       INDEX: RTM
   IPSSTAT_WIN      IPSSTAT_WIN

The overall cost is higher than the original statement :(

Thanks