Re: Efficient SQL statement needed

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 25 Jun 1998 14:50:39 GMT
Message-ID: <3596625f.7461038@192.86.155.100>

A copy of this was sent to "Bjørn Sindre Slåtto" <bsl_at_provida.no> (if that email address didn't require changing) On Thu, 25 Jun 1998 16:14:47 +0200, you wrote:

>Is is possible to summarise field A grouped by field B but with the newest
>record excluded within each group (field C contains the date YYYYMMDD)?
>
>This SQL statement has to take care of 600 000 records.
>

so you have ( a int, b int, c date ) for example....

select b, some_aggregate(a)
from t a
where exists ( select null

                 from t b
                where b.b = a.b
                  and b.c > a.c )

group by b
/

The correlated subquery will prevent a record for a given B that doesn't have a record with a GREATER c from being part of the result set (a record for b=5, c=01-Jan-98 will only be in the result set if a record with b=5 and c>01-jan-98 exists in the table)...

If there exists an index on (b,c) in the table then...

This query will FULL SCAN T and for each row in T perform an index probe on that index until it finds a record that satisfies the criteria stopping when it finds the first one.

If there is not index on (b,c) then... the query plan will depend on your environment and optimizer mode. In general it might be FULL SCAN T and for each row in T, FULL SCAN T again until you find a match (meaning for each discrete value of B, you will perform at least one really FULL SCAN again)...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jun 25 1998 - 09:50:39 CDT