select over partiton by ... order by .. used long time

Question

select a.id
from (select /*+index(test_table, test_index)*/ 
             row_number() over (partition by a, b, c order by d desc) rn,
             id
      from test_table
     ) a
where a.rn = 1

test_index(a, b, c, d) 
limit access to 500, cost 9s) 

How should I fix it

Answer 1

Out of curiosity, how long does this take with a correlated subquery instead?

select t.id
from test_table t
where t.d = (select max(t2.d)
             from test_table t2
             where t2.a = t.a and t2.b = t.b and t2.c = t.c
            );

Or using aggregation?

select max(t.id) keep (dense_rank first order by d desc)
from test_table t
group by a, b, c;