OraFAQ Forum: Performance Tuning » Performance issue with Analytical function

Home » RDBMS Server » Performance Tuning » Performance issue with Analytical function

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance issue with Analytical function [message #181174]

Fri, 07 July 2006 03:20

apramodh
Messages: 8
Registered: March 2006
Location: Texas, USA

Junior Member

Hi I have 2 questions here:

1) I have a query which should run on tables with 40 million rows and I run it on Toad/Golden and SQLPLUS.

On toad the results are displayed in milliseconds, while in SQLPLUS the results are displayed but slowly over a long period scrolling over the screen. In Golden it runs for a very long period. Is SQLPLUS and Golden is the time lag only because of the time taken to display the results on screen? Which is providing the correct answer?

2) 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) do in this case. Please advice

Thanks
Pramodh

Report message to a moderator

Re: Performance issue with Analytical function [message #181196 is a reply to message #181174]

Fri, 07 July 2006 06:21

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

1. If I remember correctly, TOAD implicitly applies a FIRST_ROWS hint to your query, whereas SQL*Plus patiently waits for the last row. Hence your perceived difference in performance.

2. Why are you tacking a row number onto every row in your result set? I mean, what's the point? Are you trying to do something like this?

Report message to a moderator

Re: Performance issue with Analytical function [message #181319 is a reply to message #181196]

Sat, 08 July 2006 11:05

apramodh
Messages: 8
Registered: March 2006
Location: Texas, USA

Junior Member

Hi,

Thanks a lot for the answer.

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.

Will the query suggested by you work for this case. I will try the same out ..but can you provide some info on this.

thanks in advance,
Pramodh

Report message to a moderator