0

I'm wondering what can be the most efficient query to get the latest rows from the following table.

CREATE TABLE AGE_DATA
(
  INSERTED_DAY TIMESTAMP(6),
  HOSTNAME VARCHAR2(255 CHAR),
  CONCEPT  VARCHAR2(255 CHAR),
  REPORTED_VALUE NUMBER(10)
);

If I want to know the latest data inserted by hostname, I have 2 approaches, and I want to know which would be the most efficient, depending the cases a) having ~100 rows, b) having more than ~1M rows.

with latest as (select hostname,max(inserted_day) inserted_day 
from AGE_DATA group by hostname)
select * 
from latest l
join age_data ad on (l.hostname = ad.hostname and l.inserted_day = ad.inserted_day)

or

select * from age_data 
where (hostname,inserted_day) in (select hostname,max(inserted_day) inserted_day 
    from AGE_DATA group by hostname)
  • Which RDBMS are you using? – Philᵀᴹ Mar 18 '15 at 10:25
  • This looks like Oracle. I suggest creating a test table and collecting execution plans and traces for both the queries. It can be that the with-clause leads to a materialization of the subquery which can have some IO overhead. There's an oracle hint to turn it off. – Falcon Mar 18 '15 at 10:31
  • @Phil it is oracle, but I would like to make it agnostic, so it can be used in other RDBMS too. – user4951 Mar 18 '15 at 11:00
1

There is a 3rd option, using window functions, either max():

with latest as 
(select *,
        max(inserted_day) over (partition by hostname)
          as max_inserted_day 
 from age_data
)
select *              
from latest l
where inserted_day = max_inserted_day ;

or rank():

with latest as 
(select *,
        rank() over (partition by hostname order by inserted_day desc)
          as rnk
 from age_data
)
select *              
from latest l
where rnk = 1 ;

If there are ties (rows with same maximum inserted_day for a hostname), all the above queries (and yours) will return all the tied rows. If you want to resolve ties (and return only 1 or only 2 rows), you can replace rank() with row_number() and change the order by clause inside the over according to the requirements.

Regarding efficiency, an index on (hostname, inserted_day) will help, in all the queries.

  • I won't bother for the ties, to avoid that I would have used unique keys. Interesting proposal. – user4951 Mar 18 '15 at 11:02
  • Yes, if there is a unique constraint on (hostname, inserted_day), all the above (max, rank, row_number) will return the same result. – ypercubeᵀᴹ Mar 18 '15 at 11:15
  • 1
    I love the windowing function/cte combination. So much power, so easy to read – Philᵀᴹ Mar 18 '15 at 12:01
  • Just found your alternative elegant. Thanks! – user4951 Mar 18 '15 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy