Performance issue with Analytical function

Hi,

I have a query which should run on tables with 40 million rows

To the above query when add the analytical function row_number() for a sorting requirement it runs indefinitely. All indexes are being used etc. but is continues to run indefinitely. What could be the possible reason? What could a developer (no DBA rights on database) do in this case.

A little more detail:

I join 3 tables to fetch subscription_id and queue_positions for a subscriber with some queuepositions missing, say -

subscriber queue_position
100 1
100 3
100 5
200 1
200 5

I use row_number () over(partition by subscriber id, order by queue position asc)to get the following output:

subscriber queue_position new_queue_pos
100 1 1
100 3 2
100 5 3
200 1 1
200 5 2

Want to order queue positions is order by covering up for missing queue positions.

Is there any other way other than using analytical functions?

Thanks in advance
Pramodh Received on Mon Jul 10 2006 - 00:22:48 CDT