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)
select * from age_data where (hostname,inserted_day) in (select hostname,max(inserted_day) inserted_day from AGE_DATA group by hostname)