Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, jakub.

Asked: November 26, 2005 - 8:53 pm UTC

Last updated: November 27, 2005 - 10:40 am UTC

Version: 10.0.1

Viewed 1000+ times

You Asked

Hello,
I need to select random rows from a table and then to remember the choice. To do this I tried to save the dbms_random.value but it doesn't work. This query always returns rows ordered the same way, doesn't matter what numbers you input:

select * from persons ORDER BY to_number('0,281910187889036');

(or 0,821836514405034, the number is taken from: select dbms_random.value from dual)

BUT this query:

select * from persons ORDER BY to_number(to_char(dbms_random.value));

works fine.
Can you explain what happens? Can you give me any ideas how to store the choice of randomly generated rows simply?

and Tom said...

order by CONSTANT

versus

order by RANDOM_VALUE_ASSIGNED_TO_EACH_ROW


??? how can you compare them at all?

and why would you to_number(to_char( A_FUNCTION_RETURNING_NUMBER ))????
lose the to_number/to_char - unless you are trying to warm up the server room by overheating the cpu's.



You MIGHT be able to use SEED to get the same stream of random numbers returned - making this "predicable" - as long as the number of rows remains unchanged - this will likely work (if the plan ever changes, then no, it won't be the same)


scott@ORA9IR2> select ename from emp order by dbms_random.value;

ENAME
----------
ADAMS
MARTIN
WARD
KING
SCOTT
CLARK
TURNER
SMITH
JONES
BLAKE
ALLEN
JAMES
FORD
MILLER

14 rows selected.

scott@ORA9IR2> select ename from emp order by dbms_random.value;

ENAME
----------
FORD
KING
ADAMS
JONES
SCOTT
MARTIN
BLAKE
JAMES
ALLEN
SMITH
TURNER
WARD
CLARK
MILLER

14 rows selected.

without seeding the random number geneator - you get different random numbers every time.... if you use dbms_random to get a single random number, say 1234, and use that to SEED the random number generator:

scott@ORA9IR2>
scott@ORA9IR2> exec dbms_random.seed( 1234 );

PL/SQL procedure successfully completed.

scott@ORA9IR2> select ename from emp order by dbms_random.value;

ENAME
----------
ALLEN
JAMES
WARD
FORD
JONES
CLARK
SMITH
MARTIN
BLAKE
ADAMS
KING
SCOTT
TURNER
MILLER

14 rows selected.

Every time you use that same seed, you'll get the same series of random numbers:

scott@ORA9IR2> exec dbms_random.seed( 1234 );

PL/SQL procedure successfully completed.

scott@ORA9IR2> select ename from emp order by dbms_random.value;

ENAME
----------
ALLEN
JAMES
WARD
FORD
JONES
CLARK
SMITH
MARTIN
BLAKE
ADAMS
KING
SCOTT
TURNER
MILLER

14 rows selected.



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks for answer

jakub, November 27, 2005 - 11:19 am UTC

ASSIGNED_TO_EACH_ROW was a key word. I didn't know that.

Of course I didn't want to use that
select * from persons ORDER BY to_number(to_char(dbms_random.value));
I'm hot enough:)

Thank you



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library