Is there a better way to retrieve a random row from an Oracle table?

user_11647488 0
Viewed 71

Not so long ago I needed to fetch a random row from a table in an Oracle database. The most widespread solution that I've found was this:

SELECT * FROM
( SELECT * FROM tabela WHERE warunek
ORDER BY dbms_random.value )
WHERE rownum = 1​

However, this is very performance heavy for large tables, as it sorts the table in random order first, then grabs the first row.

Today, one of my collegues suggested a different way:

    SELECT * FROM (
    SELECT * FROM MAIN_PRODUCT
    WHERE ROWNUM <= CAST((SELECT COUNT(*) FROM MAIN_PRODUCT)*dbms_random.value AS INTEGER)
    ORDER BY ROWNUM DESC

) WHERE ROWNUM = 1;

It works way faster and seems to return random values, but does it really? Could someone give an insight into whether it is really random and behaves the way as expected? I'm really curious why I haven't found this approach anywhere else while looking, and if it is indeed random and way better performance wise, why isn't it more widespread?

random oracle sql
3 Answers

This is the (possibly) the most simple query possible to get the results.
But the SELECT COUNT(*) FROM MAIN_PRODUCT will table scan i doubt you can get a query which does not do that.

P.s This query assumes not deleted records.

Query

SELECT * 
FROM
 MAIN_PRODUCT 
WHERE
 ROWNUM = FLOOR(
   (dbms_random.value * (SELECT COUNT(*) FROM MAIN_PRODUCT)) + 1
 )

FLOOR( (dbms_random.value * (SELECT COUNT(*) FROM MAIN_PRODUCT)) + 1 )

Will generate a number between between 1 and the max count of the table see demo how that works when you refresh it.

Oracle12c+ Query

SELECT * 
FROM
 MAIN_PRODUCT 
WHERE
ROWNUM <= FLOOR(
   (dbms_random.value * (SELECT COUNT(*) FROM MAIN_PRODUCT)) + 1
)
ORDER BY 
 ROWNUM DESC
FETCH FIRST ROW ONLY
user_25481470

The key to improving performance is to lessen the load of the ORDER BY.

If you know about how many rows match the conditions, then you can filter before the sort. For instance, the following takes about 1% of the rows:

SELECT *
FROM (SELECT *
      FROM tabela
      WHERE warunek AND dbms_random.value < 0.01
      ORDER BY dbms_random.value
     )
WHERE rownum = 1​ ;

A variation is to calculate the number of matching values. Then randomly select a smaller sample. The following gets about 100 matching rows and then sorts them for the random selection:

SELECT a.*
FROM (SELECT *
      FROM (SELECT a.*, COUNT(*) OVER () as cnt
            FROM tabela a
            WHERE warunek
           ) a
      WHERE dbms_random.value < 100 / cnt
      ORDER BY dbms_random.value
     ) a
WHERE rownum = 1​ ;
user_11440350

The second code you have

    SELECT * FROM (
    SELECT * FROM MAIN_PRODUCT
    WHERE ROWNUM <= CAST((SELECT COUNT(*) FROM MAIN_PRODUCT)*dbms_random.value AS INTEGER)
    ORDER BY ROWNUM DESC

) WHERE ROWNUM = 1;

is excellent, except that it will get subsequent elements. dbms_random.value is returning a real number between 0 and 1. Multiplying this with the number of rows will provide you a really random number and the bottleneck here is counting the number of rows rather then generating a random value for each row.

Proof

Consider the

0 <= x < 1

number. If we multiply it with n, we get

0 <= n * x < n

which is exactly what you need if you want to load a single element. The reason this is not widespread is that in many cases the performance issues are not felt due to only a few thousands of records.

EDIT

If you would need k number of records, not just the first one, then it would be slightly difficult, however, still solvable. The algorithm would be something like this (I do not have Oracle installed to test it, so I only describe the algorithm):

randomize(n, k)
    randomized <- empty_set
    while (k > 0) do
        newValue <- random(n)
        n <- n - 1
        k <- k - 1
        //find out how many elements are lower than newValue
        //increase newValue with that amount
        //find out if newValue became larger than some values which were larger than new value
        //increase newValue with that amount
        //repeat until there is no need to increase newValue
    while end
randomize end

If you randomize k elements from n, then you will be able to use those values in your filter.

user_4365600
Related