November 9th, 2013, 07:31 AM
My over query is too slow. how to index
How to index following sql. It is too slow.
select * from(
select sum(1) over (partition by code order by date desc) as rown,code, date, last,ref as yesterday_price from timestampdata ) as dsd where rown =1
November 9th, 2013, 08:28 AM
Please read this:
and then enhance your question accordingly.
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions:
November 10th, 2013, 10:18 AM
You have a SUM(1) over an ordered partition and you select only the values of 1, so I'm guessing that you only want to get the first record per "code"?
Then try RANK() instead of SUM(1) because SUM(1) cannot be predicted by the database, RANK() can (using the indexes on code and date).