Re: SQLServer beats 8i finding top-10 duplicates

I should have said

My time was 28 seconds on a 200 Mhz PC. The distribution was normal (or maybe slightly Poisson) centred on 9.5. Top occurrence was 28.

Sort_area_size 6M
Sort_area_retained_size 3M

Comparisons in 1st pass 15,000,000 (which is most of the CPU). Output from 1st pass 99,995 rows. Comparisons in second
pass 100,000

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jonathan Lewis wrote in message ...
>Interesting result Connor,
>but I think you may have handicapped
>Oracle somewhat. The original post says
>1,000,000 integers, randomly distributed
>between 1 and 100,000.
>
>Your result looks too flat to be truly
>random. Since GROUP BY is strongly
>affected by the density of sorted data,
>you may have given Oracle the worst
>possibly sorting job to do.
>
>It would be interesting to know what random
>function was used in the original post, or
>at least to know what the output was.
>
>BTW - what did you set the sort area size and
>retained size to keep it all in memory ?
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
>Connor McDonald wrote in message <3876B811.383_at_yahoo.com>...
>
>>SQL> select count(*) from testab
>> 2 /
>>
>> COUNT(*)
>>---------
>> 999999
>>
>> real: 671
>>
>>SQL> select * from
>> 2 ( select count(num) from testab group by num order by count(num)
>>desc )
>> 3 where rownum < 10
>> 4
>>SQL>
>>SQL> /
>>
>>COUNT(NUM)
>>----------
>> 11
>> 10
>> 10
>> 10
>> 10
>> 10
>> 10
>> 10
>> 10
>>
>>9 rows selected.
>>
>> real: 20860
>>
>
>
>
Received on Sat Jan 08 2000 - 12:15:56 CST