Re: How to randomise select results

Not an immediate answer to your question, but Oracle 8.1.6 has a SAMPLE function.
This picks a random fraction of a table.

If you know the size of the table, you can then sample a slightly generous sample
and stop after 5 rows.

A method that will work in 8.0.5 and 8.1.5 is to use the dbms_random you mentioned, generate suitably scaled random numbers, convert to rowids and select /*+ rowid */ where rowid in (list of rowids).

This, I believe, is roughly what the SAMPLE clause does in 8.1.6 using the bitmap to rowid conversion method to convert a random number to a rowid.

--

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

Practical Oracle 8i:  Building Efficient Databases

Publishers:                 Addison Wesley Longman
Book bound date:     8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i

Jacqui Caren wrote in message <8FE69503FJacquiCarenigcouk_at_158.152.254.67>...


>[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