DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: top 100

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    top 100

    How does Oracle handle a query that selects the top 100 rows?

    If I have a query that returns 2000 rows and I edit the query so that it brings back the top 100 rows, will this improve the performance hit on the oracle server or in the background is oracle still fetching all 2000 rows and only showing the top 100?

    I'm just looking for some clarification on reducing the load on wide ranging queries.


    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It would depend how you wrote the SQL to retrieve the Top 100, and how Oracle optimises it.

    A poular way is something like ...

    Code:
    Select *
    From   (
           Select ...
           From
           Order By some_column desc
           )
    Where  rownum <= 100
    If there is an index on some_column that Oracle can read to order the rows then getting the top 100 would indeed be "cheaper" than the Top 2000. However if Oracle had to go and sort 100,000 rows of data in order to find the top values then the cut-off point (100 or 2000) would be much less significant in determining the work done.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    It depends. Your "top 100" implies a sort; if this can be achieved by reading an index, it might only have to read 100 rows - so much depends on the where clause.

    You do reduce the network load.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    slim, we must stop meeting like this
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: top 100

    Originally posted by luciffer
    How does Oracle handle a query that selects the top 100 rows?

    If I have a query that returns 2000 rows and I edit the query so that it brings back the top 100 rows, will this improve the performance hit on the oracle server or in the background is oracle still fetching all 2000 rows and only showing the top 100?

    I'm just looking for some clarification on reducing the load on wide ranging queries.


    Thanks
    Can you post the explain plan?

    I think that you will find that it needs to pull back all of the rows meeting the criteria, sort them and grab the top n rows. The exception would be if you added a criteria on an indexed column you might get a range scan and thus not look at all of the rows. i.e. have an non unique index on a price field, and try to get the top n prices but you know that the value would be greater than $100.

  6. #6
    Join Date
    Dec 2003
    Posts
    90
    I can't post the explain plan, but the query is used to search for employees by either employee first name, last name, phone number, business name or business registration number or any combination there of.

    So if i call the stored procedure with only lastname parameter as "smith" the query would look like

    Select bu.name, bu.reg_num, prs.fname, prs.lname, prs.phone
    From business bu, person prs, bu_prs
    Where prs.id = bu_prs.prs_id
    And bu.id = bu_prs.bu_id
    And prs.lname = "smith"

    Which could potentially get 2000 records from the DB, but i only care to return the first 100 (no particular order) just to limit the "performance hit" on the DB server.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If random order is acceptable (!) then ROWNUM<=100 should reduce the load. The amount by which it is reduced may not be noticable (as always, it depends).
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think that if you don't enforce an order (even a meaningless one, like employee_id) then you're going to get questions down the road about the order "changing". Thoughts?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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