-
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
-
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.
-
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
-
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
-
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.
-
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.
-
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
-
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?
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
|