2

Possible Duplicate:
How to request a random row in SQL?

Hi I am trying to select random records from a oracle database. I tried using the query below:

select field1 
from (select field1 from table1 where field2='SAMPLE_VALUE' order by dbms_random.value) 
where rownum <2;

This works just fine, except that it's extremely slow and expensive.

Do we have any quicker way to achieve this?

Community
  • 1
  • 1
Leo
  • 171
  • 1
  • 3
  • 11

3 Answers3

2

You could try the SAMPLE clause:

select field1
from   table1 sample (1)
where  field2='SAMPLE_VALUE' and
       rownum <=1

However:

  1. You're probably not going to get index-based access
  2. there's no guarantee you'd get enough values
  3. You'd probably tend to get values from the "beginning" of the table.
David Aldridge
  • 48,793
  • 8
  • 60
  • 88
  • What if there is no record with `field2='SAMPLE_VALUE'` in the 1% ? – A.B.Cade May 15 '12 at 09:49
  • As I say, there's no guarantee that you'd get enough values, particularly for very selective queries. However if the DBMS_Random approach is very slow and expensive then I'd expect that to be because there are a great many records to be sorted so the chances of finding some ought to be reasonably good in this case. – David Aldridge May 15 '12 at 10:09
0

If records has some unique numeric column, I would try to compare it to randomly generated value. Something like SELECT ... FROM ... WHERE id = RANDOM_VALUE. But some calculations may be needed to have the same range for this unique column and for random value.

psur
  • 4,113
  • 23
  • 34
0

You need to check execution plan.
It's probably doing a full access to the table, so if you can add an index on field2 this might make it worth for the optimizer to do an index range scan according to your where clause.
(I don't really know about field2 anything, so check it carefully)

A.B.Cade
  • 16,267
  • 1
  • 35
  • 51