-
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?
Able was I ere I saw Elba
-
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
Able was I ere I saw Elba
-
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
Able was I ere I saw Elba
-
If the index on salary is not being used then try including a FIRST_ROWS hint in the inner query
-
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
Last edited by tamilselvan; 11-15-2005 at 02:52 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|