Re: Question about MIN/MAX optimization

Hi!

I tested it on a similar table

Running the  inner part  ( select abc from T were id  = ?)  has a cost of
15 timerons
Still db2 arrives at the same type of  accesplan as you had  with a cost
of  30 trs

My table has only 70000 rows. Maybe you get a better plan?
/dg

"Dan van Ginhoven" <danfan46@[EMAIL PROTECTED]
> wrote in message
news:6KnQj.6546$R_4.5328@[EMAIL PROTECTED]
> 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
>
>
>
> "Michel Esber" <michel@[EMAIL PROTECTED]
> wrote in message
news:d2de1662-4359-4c17-90b0-66daa5f4fc51@[EMAIL PROTECTED]
> On 25 abr, 12:52, Serge Rielau <srie...@[EMAIL PROTECTED]
> wrote:
> > Try this then:
> > SELECT *
> > FROM (SELECT abc FROM T WHERE ID = ?
> > ORDER BY id DESC, abc DESC FETCH FIRST ROW ONLY) AS Z,
> > (SELECT abc FROM T WHERE ID = ?
> > ORDER BY id ASC, abc ASC FETCH FIRST ROW ONLY) AS Y
>
>
> I think it did not help. The plan is a much higher cost:
>
> Access Plan:
> -----------
>         Total Cost:             64906.6
>         Query Degree:           1
>
>               Rows
>              RETURN
>              (   1)
>               Cost
>                I/O
>                |
>                 1
>              NLJOIN
>              (   2)
>              64906.6
>              43706.8
>           /-----+-----\
>         1                1
>      IXSCAN           IXSCAN
>      (   3)           (   4)
>       32453           32453.6
>      21853.4          21853.4
>        |                |
>    1.48278e+06      1.48278e+06
>  INDEX: RTM       INDEX: RTM
>    IPSSTAT_WIN      IPSSTAT_WIN
>
>
>
>
> Extended Diagnostic Information:
> --------------------------------
>
> No extended Diagnostic Information for this statment.
>
>
> Plan Details:
> -------------
>
>
>         1) RETURN: (Return Result)
>                 ***ulative Total Cost:          64906.6
>                 ***ulative CPU Cost:            2.9567e+09
>                 ***ulative I/O Cost:            43706.8
>                 ***ulative Re-Total Cost:       1008.61
>                 ***ulative Re-CPU Cost:         2.66915e+09
>                 ***ulative Re-I/O Cost:         0
>                 ***ulative First Row Cost:      51.2763
>                 Estimated Bufferpool Buffers:   43707
>
>
> Thanks for your inputs, Michel.
>
>