| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: query for top 10 sql?
for sorting 10 out of 1000 you indeed need to SCAN the whole set, but you do
not need to SORT the whole set; you only need a temporary array of length 10
to maintain the ten largest values encountered so far. any lower values can
immediately be discarded, any high values just kick out one of the existing
temp array values. this is much cheaper than sorting ...
hope this helps,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of jo_holvoet_at_amis.com
Sent: vrijdag 12 maart 2004 9:27
To: oracle-l_at_freelists.org
Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
Subject: RE: query for top 10 sql?
In my version (new one) it's in "Chapter 8: Effective SQL", paragraph "Top-N Query Processing with ROWNUM".
mvg/regards
Jo
Igor Neyman <ineyman_at_perceptron.com>
Sent by: oracle-l-bounce_at_freelists.org
03/11/2004 22:32
Please respond to oracle-l
To: oracle-l_at_freelists.org
cc:
Subject: RE: query for top 10 sql?
It's in "Effective Oracle".
Don't have book with me to reference the page.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala
Sent: Thursday, March 11, 2004 1:44 PM
To: oracle-l_at_freelists.org
Subject: Re: query for top 10 sql?
On 03/11/2004 10:39:05 AM, Igor Neyman wrote:
> Obviously in this case (second out of 3) it doesn't make any
difference.
> But, if asked for top 10 out of 1000:
> - Without optimization: all 1000 would have been sorted;
> - With optimization: find top 10 and stop, no need to sort the rest
990.
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com
>
>
Quite frankly, I still don't know which book are you referring to,
"effective oracle" or "101"
and, if "1-on-1", which edition? I'm asking because things don't look
very logical to me,
so I'd like to read them. I'd be thankful if you specified the book more
closely.
It doesn't look logical to me to sort only 10 out of 1000 if I want to
find the 10 maximal ones.
Namely, to find 10 largest, I need to sort them all. I can stop
producing output after 10th
row, but I must sort them all, or use some fancy sort algorithm,
significantly slower then
topological sort or quick sort (see Knuth for the description of those).
Tom's explanation
of those seemingly contradicting facts is probably wonderful and I'd
like to read it. As I have
all 3 of his books, I'd appreciate if you could reveal your source.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Mar 12 2004 - 02:55:29 CST
 |
 |