Prev: SELECT INTO with ORDER BY and FETCH FIRST ONLY
Next: how to view a vertical table in horizontal way
From: Hardy on 22 Jan 2010 20:47
On 1ÔÂ19ÈÕ, ÉÏÎç1ʱ59·Ö, Michel Esber <smes...(a)gmail.com> wrote:
> Hello,
>
> Db2 UDB v9.5 Fp3.
>
> I need to return the set of rows that have the highest timestamp. This
> is very easy:
>
> drop table DATA;
>
> create table DATA (sample timestamp, field1 integer);
>
> insert into DATA values ('2010-01-18-14.00.00.000000', 1),
> ('2010-01-18-14.00.00.000000',2), ('2010-01-18-14.00.00.000000',3),
> ('2010-01-18-14.01.00.000000',4), ('2010-01-18-14.01.00.000000',5),
> ('2010-01-18-14.02.00.000000',6), ('2010-01-18-14.02.00.000000',7);
>
> Easy query:
>
> select * from DATA D,
> (select MAX(SAMPLE) MAX_SAMPLE from DATA) as M
> where D.SAMPLE = M.MAX_SAMPLE;
>
> My source table has hundreds of Millions of rows and the access plan
> shows two index scans.
> Is there any magic trick to achieve the same result with only one
> index scan?
>
> Thanks in advance, -Michel


Pls don't chase less scans too much. With a index on the sample
column, you have the best solution at the begining.
The way of rank, max implict processes too many rows than you actually
need, much more resource cost, much more time cost.
You can test how they degrade your SQL performance.

In field support, I strongly suggest limit the use of the usage of
olap function in case you are only want "top" 1 or 2 rows. The olap
functions are too easy to use and some developers abuse its usage.
First  |  Prev  | 
Pages: 1 2 3
Prev: SELECT INTO with ORDER BY and FETCH FIRST ONLY
Next: how to view a vertical table in horizontal way