Re: Performance issue with Analytical function

apramodh_at_gmail.com wrote:
> 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

Do you really need the 'queue position' to be continuous? Could you not live with

order by subsciber, queue_position asc Received on Mon Jul 10 2006 - 03:42:02 CDT