Michel Esber wrote:
> Hi all,
>
> Db2 v8 FP15 LUW .
>
> create table T (ID varchar (24), ABC timestamp)
>
> There is an index for (ID, ABC), allowing reverse Scans.
>
> My application needs to determine MIN and MAX(ABC) for a given ID. We
> are currently using a simple statement:
>
> select MIN(abc), MAX(abc) from T where ID = ? for read only
>
> Table T has 100+ million rows, and several other applications are
> reading/deleting data from it. The statement above runs with UR
> isolation, however it takes a very long time to complete (5-10
> minutes, or more).
>
> I have studied the access plan, and it looks OK:
>
> 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
>
>
> I am looking for some magic SQL or hint that will allow me to improve
> this rather 'simple' query.
Try this:
SELECT *
FROM (SELECT abc FROM T WHERE ID = ?
ORDER BY abc DESC FETCH FIRST ROW ONLY) AS Z,
(SELECT abc FROM T WHERE ID = ?
ORDER BY abc ASC FETCH FIRST ROW ONLY) AS Y

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab