How to retrieve random records from Oracle with dbms_random

Question

I am a beginner in sql and I was wondering if you could help me figure out how to retrieve random records from Oracle. I have the following query:

SELECT p.* from data p where p.id in 
(SELECT DISTINCT t.id   FROM DATA t, comment c, PTV ptv   
WHERE t.code not in ('x','y','Z') 
and t.id = c.id(+)   and ptv.code = t.code   and ptv.code = t.code   
and ptv.version = t.version  AND t.TASK_CODE != 'DRAFT')

How can i select X number of random records from the results returned by the above query? Any suggestions will be appreciated.

Unrelated, but: stop using implicit joins in the where clause especially for outer joins (the (+) operator). Use an explicit JOIN or LEFT JOIN operator. Even Oracle recommends to stop using the (+) operator. — user330315
– user330315, Commented Aug 20, 2014 at 11:14
On a side note. @a_horse_with_no_name In fact, there is nothing wrong with using oracle native(not ANSI) join syntax, it's just a matter of personal preferences to some extent. Does ANSI join provide more clarity, easiness and allows you to write more portable statements? Yes. I personally do prefer ANSI join syntax to the traditional one for that. But at the end of the day ANSI syntax will be transformed to the traditional one by the optimizer, anyway. — Nick Krasnov
– Nick Krasnov, Commented Aug 20, 2014 at 11:47
@NicholasKrasnov: You are right, there is nothing "wrong" using a 20year old syntax that has been superseeded with a newer standard. One advantage of the explicit JOIN operator is that you can't accidentally create a cross join just because you forget the join condition. Plus there are things you can't do with the (+) operator compared to the LEFT JOIN operator (as I said: Oracle recommends to not use (+) any more) — user330315
– user330315, Commented Aug 20, 2014 at 11:50
@Nicholas Krasnov not true. I was stated by Oracle that some newer optimizer constructs are usable only with new the syntax. (But on contrary - if I remember correctly - somewhere in some special case you can use only (+)). — ibre5041
– ibre5041, Commented Aug 20, 2014 at 12:22

Gordon Linoff · Accepted Answer · 2018-01-13 15:09:44Z

3

The "easy" way to get a random number is to use dbms_random.value or dbms_random.random() and rownum:

with t as (
      <your query here>
     )
select x.*
from (select t.*
      from t
      order by dbms_random.value
     ) x
where rownum < 10;

I suspect there are better ways to write your query. If you want suggestions on that front, write another question, include sample data, desired results, and describe what you want the query to do.

edited Jan 13, 2018 at 15:09

answered Aug 20, 2014 at 11:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

0xdb Over a year ago

I'm sorry, but what is dbms.random in order by? ORA-00904: "DBMS"."RANDOM": invalid identifier.

ibre5041 · Accepted Answer · 2014-08-20 11:38:27Z

2

Use SAMPLE clause:

select * from data t SAMPLE(1) left outer join comment c on (t.id = c.id)

answered Aug 20, 2014 at 11:38

ibre5041

5,3261 gold badge24 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to retrieve random records from Oracle with dbms_random

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related