Fine tuning SQL query

[tamilselvan]

Hi,

I know this query will work to find the first N highest salaries:

select * from employee
where salary in
(select salary
from employee
order by salary desc)
where rownum <= 5

But this simply goes for a toss with a table having more than a million records and say if I want to see the first 1000 highest salaries. I mean the query becomes very very slow. Obviously, because it has to do a full table scan to first sort them in descending order. Even having indexes has not helped.

Can you please let me know how to fine tune this query to speed up the process?

Many thanks

No need to have IN clause.
Your query must be rewritten like:

select * 
from ( select * from employee
         order by salary desc) 
where rownum <=5 ;

Assume you have an index on salary column.
Some times you have to lie the optimizer.
You can try one of the 2 SQL given below:

select * 
from  (select * from employee 
         where salary > 0 
         order by salary desc)
where rownum <= 5; 

var B1 number; exec :b1:= 0;
select * 
from  (select * from employee 
         where salary > :b1 
         order by salary desc)
where rownum <=5 ;

Here, index will be range scanned.

If you do not have an index on salary, then
select * 
from (select /*+ full(a) parallel(a 8) */ *
           from employee a
         order by salary desc) 
where rownum <= 5;

Tamil