Fine tuning SQL query

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

Try this one:

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

do you have to select ALL?

Quote:

---

Originally Posted by robertbalmer

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.

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

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

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

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.

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

If the index on salary is not being used then try including a FIRST_ROWS hint in the inner query

Quote:

---

Originally Posted by 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

---

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

Code:

---

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:

Code:

---

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

Thanks for the replies guys. I think TamilSelvan's suggestions has been quite useful to improve the performance. Can we use the RANK function anyway to get the same results? Would this improve the performance?