Re: How to randomise select results

In article <8FE69503FJacquiCarenigcouk_at_158.152.254.67>,   Jacqui.caren_at_ig.co.uk (Jacqui Caren) wrote:
> [posted and mailed]
>
> After doing a some investigation I cannot find a effecient
> solution to this problem and hope people here could help.
>
> Under Oracle 8.0.5. or 8.1.5 we currently select a number
> of records (say 5) from a list of many and then display these
> in a HTML table. This type of activity happens in many places
> almost every page hit...
>
> The problem is that in order to randomly select 5 we need to
> download all records then select 5 randomly. With a large
> and growing number of records this is starting to have a
> performance hit...
>
> If we could create a SQL cursor that would randomise the
> order of the results we could bind the cursor to an
> already existsing table object that would render a table
> consisting of only the first N (say 5) records, closing
> the cursor at that point. This as far as the system goes
> should (in theory) be much more effecient - esp.y when
> the number of records start to grow significantly say
> 100,000 or more.
>
> What I am looking for is a way to randomise the order
> of a select statement that does not significantly impact
> the queries performance.
>
> I have found the DBMS_RANDOM code on 8.1.5 but this
> does not seem very effecient - comments are welcome.
>
> We also looked at placing a trigger upon the table to
> increment counters for each record selected - this would
> lead to records getting an even visibility distribution
> but requires modifying the database, adding triggers etc
> and again impacts performance and has other drawbacks.
>
> If you wish to email me direct I will be happy to
> summarise back to the group, otherwise please followup
> here...
>
> Thanks in advance.
>
> Jacqui Caren
>

I would avoid trying to randomize the *order* of the records retrieved. While you could do this with a sysdate-based hash function, you would still be sorting 100,000 records to get 5. Jonathan Lewis' solution seems much more efficient.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 16 2000 - 17:59:24 CST