Fine tuning SQL query

[Vipassana]

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

[robertbalmer]

Try this one:

select * from employee
where rownum<=5
order by salary

do you have to select ALL?

[Vipassana]

Try this one:

select * from employee
where rownum<=5
order by salary

do you have to select ALL?

This wont give me the first highest.

[robertbalmer]

oops! but your query doesnt execute either. will check and come back

[Vipassana]

The query should be
select * from employee
where salary in
(select salary
from employee
order by salary desc)
and rownum <= 5

[mike9]

If you want the 5 top employee ordered by salary

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


If you want all employees having the 5 top salaries

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

OR

select * from (
select e.*,
dense_rank() over (order by salary desc) as dr
from employee e
)
where dr <=5

[Vipassana]

mike9: I think you didnt' get my point. U have more or less given the same solution. We need to avoid the order by salary desc in the sub-query. Else the query wull go for a toss if there are a million records and if I want to see say first 10000 highest saalries.

[robertbalmer]

couldn't you build an index on salary+anther relational column like occupation or % comm. Just make a new temp column where occupation is represented by number and not varchar? I can only think of indexing to solve your problem. Not much to change on your query

[slimdave]

If the index on salary is not being used then try including a FIRST_ROWS hint in the inner 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