0
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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
zfy
  • 1
  • 1
    What do you want to fix? Please, clarify your question: describe your current situation and what exactly is wrong with it, add [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) or at least a [query plan](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database/34975420#34975420) and what steps you have already performed to resolve the issue. – astentx Jun 28 '21 at 10:29
  • I would remove that hint for a start. There is no where clause against your test_table, so a full table scan is likely to be quicker than forcing it to use an index (unless your index actually has all the columns in it, and at least one of the columns is not nullable). Index scans are not always good, and full table scans are not always bad. – Boneist Jun 28 '21 at 15:03

1 Answers1

2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • i have test select max(t.id) keep (dense_rank first order by d desc) from test_table t group by a, b, c; cost 4s – zfy Jul 02 '21 at 08:19