How to randomise select results

[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 Received on Wed Nov 08 2000 - 08:23:08 CST