Re: Question about MIN/MAX optimization

Michel Esber wrote:
>> This is the optimal plan we want. Can you reproduce it on DB2 V8 with
>> this DDL and DML?
> 
> Here is a v8 plan using your solution. I will post another message
> with the plan for MIN/MAX and group by.
>         Total Cost:             12.846
>         Query Degree:           1
> 
>               Rows
>              RETURN
>              (   1)
>               Cost
>                I/O
>                |
>                 1
>              NLJOIN
>              (   2)
>              12.846
>                 2
>           /-----+-----\
>         1                1
>      IXSCAN           IXSCAN
>      (   3)           (   4)
>      6.42298          6.42298
>         1                1
>        |                |
>        73               73
>  INDEX: DB2INST1  INDEX: DB2INST1
>         I                I

OK, so now the question is where is the difference between your original 
scenario  (not the best plan) and mine (best plan).
I recommend morphing it step by step and see where you loose your way.
Obviosuly the first step is to add data and statistics.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab