Fine tuning SQL query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Fine tuning SQL query

Hybrid View

  1. #1
    Join Date
    Jul 2003
    Posts
    134

    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

  2. #2
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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

  3. #3
    Join Date
    Jul 2003
    Posts
    134
    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.

  4. #4
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    oops! but your query doesnt execute either. will check and come back
    Able was I ere I saw Elba

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    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

  6. #6
    Join Date
    Jul 2003
    Posts
    134
    The query should be
    select * from employee
    where salary in
    (select salary
    from employee
    order by salary desc)
    and rownum <= 5

  7. #7
    Join Date
    Jul 2003
    Posts
    134
    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.

  8. #8
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If the index on salary is not being used then try including a FIRST_ROWS hint in the inner query
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
    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
  •  


Click Here to Expand Forum to Full Width