Very simple question: “Select 1000 random accounts from MYTABLE table”
This is very common question in analytical environments, yet most of the solutions I’ve seen are far from simple and random.
You can use DBMS_RANDOM or DBMS_CRYPTO packages – both deliver similar results although some might say DBMS_CRYPTO is “more” random. I’d say use DMBS_CRYPTO if you have the grant, if not use DBMS_RANDOM (really, you won’t see that much difference).
Query below is very simple and has 3 stages (3 subqueries) – first generate random number for each record, then sort the data by random number, after that select first 1000 records.
SELECT ACCOUNT_ID FROM ( SELECT ACCOUNT_ID, RANDOM_ORDER FROM ( SELECT ACCOUNT_ID, DBMS_RANDOM.RANDOM RANDOM_ORDER FROM MYTABLE ) ORDER BY RANDOM_ORDER) WHERE ROWNUM < 1001
It really cannot be easier than that.