over partition by

[roedelfroe]

Hi,

I've written a statement, which works fine but it is rather slow.
Does anybody got a better idea?

select * from
(
select a.*, row_number() over
(partition by row1, row2 order by row3 desc) as xxx
from TABLE a
where DATE <= to_date('01.01.2009', 'DD.MM.YYYY')
and DELETED is NULL
) x
where xxx=1
order by row1, row2;

Thanks in advance

roedelfroe

 

[Dagon]

The alternative, which may work better if you have indexes on row1 and row2, is to use a subquery:

select * 
from table a
where a.row3 = 
(select max(b.row3)
from table b
where b.row1 = a.row1
and   b.row2 = a.row2
and   b.DATE <= to_date('01.01.2009', 'DD.MM.YYYY') 
and   b.deleted is null)
where a.DATE <= to_date('01.01.2009', 'DD.MM.YYYY') 
and   a.deleted is null
order by row1, row2

But it all depends on how big table is and how much data there is which is less than '01.01.2009'. If there are only a very small number of rows, it may be worth putting an index on the DATE column. If there is already an index on the DATE column and you are returning a lot of rows, then it could be the index is making things worse and you could use a hint to suppress it. Another option might be to partition the table on the Date column in some way.


For Oracle-related work, contact me through Linked-In.