> Hi.
>
> Try this. It will probably generate one indexs can instead of two.
>
> with temp (abc) as
> * * ( select abc from T were id *= ?)
> select *max (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= ? 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 = :?)) 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 = :?)) 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