Home » SQL & PL/SQL » SQL & PL/SQL » tuning of sql statement(top 25 emp highst earing) (oracle 9i)
tuning of sql statement(top 25 emp highst earing) [message #417302] Fri, 07 August 2009 02:00 Go to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
dear all i want to find out top 25 highest earing employee for one month

i have query

SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 26;


but data of emp table which contains slary is very large
its almost 50,00000 ,
query is taking 10 min for this

i want to display 25 highest earing employee in gui(java),
is it possible to tune this query, which will take minimum time


Re: tuning of sql statement(top 25 emp highst earing) [message #417312 is a reply to message #417302] Fri, 07 August 2009 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to scan the whole table and order the rows in any case.
So no way to improve it.

Regards
Michel
Re: tuning of sql statement(top 25 emp highst earing) [message #417313 is a reply to message #417302] Fri, 07 August 2009 02:41 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It shouldn't take that long - I wrote this test case and ran it on the database on my desktop, and it took 0.4 seconds:
create table test_031 (salary  number, last_name  varchar2(30));

insert into test_031 (select floor(dbms_random.value(1,1000000)),'E'||level from dual connect by level <= 500000);

SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM test_031
ORDER BY salary DESC)
WHERE ROWNUM <= 26;

You need to do some investigation work.

Additionaly, you've fallen into one of the many pitfalls that this problem represents - what if row number 23,24,25,26 & 27 all have the same salary - you're just selecting some of the rows at random.

A better way of phrasing the question would be 'Get me all the employees that earn the 26 highest distinct salaries' - and that would be solved like this:
select *
from (select last_name
            ,salary
            ,dense_rank() over (order by salary desc) rank
       from   test_031)
where rank <= 26;
Previous Topic: SQL NULL
Next Topic: Inquiry on calling procedures within a procedure
Goto Forum:
  


Current Time: Sun Dec 11 02:40:26 CST 2016

Total time taken to generate the page: 0.09744 seconds