Re: SQLServer beats 8i finding top-10 duplicates

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 10 Jan 2000 20:41:29 +0800
Message-ID: <3879D379.3FE5@yahoo.com>

Jonathan Lewis wrote:
>
> 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
> >

Hello Johnathan,

I used a linear congruential - I didn't check its period, but as with all LC generators, it would have cycled after some amount of time...

A nice healthy sort_area_size = 16M certainly assisted in the timing results. I suppose you could call that cheating a little. :-)

Cheers
Connor
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Jan 10 2000 - 06:41:29 CST