Re: Question about MIN/MAX optimization

> Try this:
> SELECT *
> =A0 =A0FROM (SELECT abc FROM T WHERE ID =3D ?
> =A0 =A0 =A0 =A0 =A0 ORDER BY abc DESC FETCH FIRST ROW ONLY) AS Z,
> =A0 =A0 =A0 =A0 (SELECT abc FROM T WHERE ID =3D ?
> =A0 =A0 =A0 =A0 =A0 ORDER BY abc ASC FETCH FIRST ROW ONLY) AS Y

Hi Serge, thanks for the quick reply.

I tried your solution, and it has virtually the same cost and plan:


Using MIN/MAX:

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

              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
                1
             NLJOIN
             (   2)
             25.6855
             3.99557
          /-----+-----\
        1                1
     GRPBY            GRPBY
     (   3)           (   5)
     12.8262          12.8587
     1.99557             2
       |                |
     46.1442          46.1442
     IXSCAN           IXSCAN
     (   4)           (   6)
     12.8544          12.8544
        2                2
       |                |
     973678           973678
 INDEX: RTM       INDEX: RTM
   IPSSTAT_WIN      IPSSTAT_WIN


        1) RETURN: (Return Result)
                ***ulative Total Cost:          25.6855
                ***ulative CPU Cost:            317747
                ***ulative I/O Cost:            3.99557
                ***ulative Re-Total Cost:       0.037816
                ***ulative Re-CPU Cost:         105574
                ***ulative Re-I/O Cost:         0
                ***ulative First Row Cost:      25.6844
                Estimated Bufferpool Buffers:   5


Using Fetch First:


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

            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
              1
           NLJOIN
           (   2)
           25.759
              4
          /---+---\
        1            1
     TBSCAN       IXSCAN
     (   3)       (   6)
     12.9045      12.8544
        2            2
       |            |
     46.1442      973678
     TEMP     INDEX: RTM
     (   4)     IPSSTAT_WIN
     12.8585
        2
       |
     46.1442
     IXSCAN
     (   5)
     12.8544
        2
       |
     973678
 INDEX: RTM
   IPSSTAT_WIN


        1) RETURN: (Return Result)
                ***ulative Total Cost:          25.759
                ***ulative CPU Cost:            443792
                ***ulative I/O Cost:            4
                ***ulative Re-Total Cost:       0.0678959
                ***ulative Re-CPU Cost:         189550
                ***ulative Re-I/O Cost:         0
                ***ulative First Row Cost:      25.7305
                Estimated Bufferpool Buffers:   3



In fact, using fetch first seems to have more CPU Cost, In terms of
performance (IO/CPU), what benefits should I expect ?

Thanks again, Michel.