Re: Performance issue with Analytical function

You always need to be a little cautious with analytic functions. Ideally you should use them on relatively small data sets as otherwise they can involve a lot of sorting.

For example - your

    > row_number () over(partition by subscriber, order by queue position asc)
has to sort the ENTIRE row source - 40 million rows, according to your note - by subscriber and position.

There are strategies you can adopt to minimise the damage on large data sets if you really need to do this sort of thing. The first thing in your case is probably to see if you can allow the session enough memory to do a more efficient sort. (My guess is that it's going multi-pass, rather than one-pass - and one-pass may be sufficient to make performance reasonable).

An alternative strategy:
Effectively, you have to run the join twice - but for one copy you only select the subscriber and positions. Apply the analytic function to this copy, then join back to the first copy on subscriber and position. I should have an example similar to this on my website some time in the next few days.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


<apramodh_at_gmail.com> wrote in message 
news:1152508968.055452.235940_at_p79g2000cwp.googlegroups.com...


> 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

>