Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: February 07, 2017 - 3:24 pm UTC

Last updated: February 10, 2017 - 5:08 pm UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Note - this is more of an academic question as I have a resolution, I am just keen to see whether my alternative approach is possible.

I have a HR table with a list of names. For demo purposes I needed to shuffle these around to make them a bit more anonymous. I.e. given the set of data:

Select 1 person_id, 'Alice' first_name, 'Jones' last_name From dual Union All
Select 2 person_id, 'Bob' first_name, 'Smith' last_name From dual Union All
Select 3 person_id, 'Charles' first_name, 'Walker' last_name From dual Union All
Select 4 person_id, 'Dave' first_name, 'Simpson' last_name From dual Union All
Select 5 person_id, 'Elaine' first_name, 'Andrews' last_name From dual

I want a list of person_id's (which may not be continuous as in the example above), with a random first name and last name. I'm able to do this in a bit of a rudimentary way:

With xxdemo_tab As( 
  Select 1 person_id, 'Alice' first_name, 'Jones' last_name From dual Union All 
  Select 2 person_id, 'Bob' first_name, 'Smith' last_name From dual Union All 
  Select 3 person_id, 'Charles' first_name, 'Walker' last_name From dual Union All 
  Select 4 person_id, 'Dave' first_name, 'Simpson' last_name From dual Union All 
  Select 5 person_id, 'Elaine' first_name, 'Andrews' last_name From dual 
) 
Select x1.person_id, x2.first_name, x3.last_name From  
  (Select Row_Number() Over (Order By dbms_random.value) n, person_id From xxdemo_tab) x1, 
  (Select Row_Number() Over (Order By dbms_random.value) n, first_name From xxdemo_tab) x2, 
  (Select Row_Number() Over (Order By dbms_random.value) n, last_name From xxdemo_tab) x3 
Where x1.n=x2.n 
  And x2.n=x3.n 
Order By person_id


However I was sure that it was possible to do it another way. I've been keen to learn the model clause for a while, so I thought that this might be a candidate for that. However I am fighting somewhat with the issue of not being able to call a function for each row, something Tom explains very clearly here: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:3181424400346795479
I managed to get something half working, but I have to hard-code in the number of possible rows to dbms_random.value().

With xxdemo_tab As(
  Select 1 person_id, 'Alice' first_name, 'Jones' last_name From dual Union All
  Select 2 person_id, 'Bob' first_name, 'Smith' last_name From dual Union All
  Select 3 person_id, 'Charles' first_name, 'Walker' last_name From dual Union All
  Select 4 person_id, 'Dave' first_name, 'Simpson' last_name From dual Union All
  Select 5 person_id, 'Elaine' first_name, 'Andrews' last_name From dual
)
Select person_id, first_name, last_name
  From xxdemo_tab
Model
  Dimension By (Row_number () Over (Order By person_id) idx)
  Measures (person_id, first_name, last_name)
  Rules Automatic Order
    (first_name [idx] = first_name[Round(Dbms_Random.Value(1,5))]);
    


Have you any suggestions on how I might be able to get this working a bit better? And how can I guarantee that dbms_random.value is called for every row because I'm not 100% sure it is at the moment - often I get all the same value in first_name, which I realise is possible with just 5 values, however seems to occur too frequently.
Ideally I'd like to do this in pure SQL without invoking PL/SQL functions. As I said, more of an academic exercise. :)

with LiveSQL Test Case:

and Connor said...

I dont think we can ever get away from at least a dbms_random call per item (ie, rows x cols )...but we can eliminate the join

SQL> WITH xxdemo_tab AS
  2    ( SELECT 1 person_id, 'Alice' first_name, 'Jones' last_name FROM dual
  3    UNION ALL
  4    SELECT 2 person_id, 'Bob' first_name, 'Smith' last_name FROM dual
  5    UNION ALL
  6    SELECT 3 person_id, 'Charles' first_name, 'Walker' last_name FROM dual
  7    UNION ALL
  8    SELECT 4 person_id, 'Dave' first_name, 'Simpson' last_name FROM dual
  9    UNION ALL
 10    SELECT 5 person_id, 'Elaine' first_name, 'Andrews' last_name FROM dual
 11    ),
 12    t AS
 13    ( SELECT 1 typ, TO_CHAR(person_id) x, dbms_random.value rnd FROM xxdemo_tab
 14    UNION ALL
 15    SELECT 2 typ, first_name , dbms_random.value FROM xxdemo_tab
 16    UNION ALL
 17    SELECT 3 typ, last_name , dbms_random.value FROM xxdemo_tab ORDER BY 3
 18    ),
 19    t1 AS
 20    ( SELECT rownum r, t.* FROM t
 21    ),
 22    t2 AS
 23    (SELECT t1.* ,
 24      row_number() over ( partition BY typ order by r ) AS rnum
 25    FROM t1
 26    )
 27  SELECT *
 28  FROM
 29    ( SELECT rnum, typ, x FROM t2
 30    ) pivot ( MAX(x) AS x FOR ( typ ) IN ( '1' AS pid, '2' AS first_name, '3' AS last_name ));

      RNUM PID_X                                    FIRST_NAME_X                             LAST_NAME_X
---------- ---------------------------------------- ---------------------------------------- ------------------------
         1 1                                        Elaine                                   Simpson
         2 2                                        Bob                                      Andrews
         4 3                                        Alice                                    Walker
         5 4                                        Dave                                     Smith
         3 5                                        Charles                                  Jones


I'm sure there's probably better solutions - others welcome to add theirs.

Rating

  (9 ratings)

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

Comments

Model?

John Keymer, February 10, 2017 - 8:21 am UTC

Thanks, I never considered using an approach like that. It took me a while to work out what it was actually doing!

For my own peace of mind, can you see any way of achieving this with the Model clause?

Alternative

Racer I., February 10, 2017 - 2:08 pm UTC

Just random, no shuffle :

WITH
t1 AS (
SELECT 1 person_id, 'Alice' first_name, 'Jones' last_name FROM dual
UNION ALL
SELECT 2 person_id, 'Bob' first_name, 'Smith' last_name FROM dual
UNION ALL
SELECT 3 person_id, 'Charles' first_name, 'Walker' last_name FROM dual
UNION ALL
SELECT 4 person_id, 'Dave' first_name, 'Simpson' last_name FROM dual
UNION ALL
SELECT 5 person_id, 'Elaine' first_name, 'Andrews' last_name FROM dual)
SELECT person_id,
MAX(first_name) OVER (ORDER BY dbms_random.value * dbms_random.value) first_name,
MAX(last_name) OVER (ORDER BY dbms_random.value * dbms_random.value) last_name
from t1
order by 1

Alternative II

Racer I., February 10, 2017 - 3:02 pm UTC

This uses the MODEL-clause (never used it before so probably way wrong) :

WITH
t1 AS (
SELECT 1 person_id, 'Alice' first_name, 'Jones' last_name FROM dual
UNION ALL
SELECT 2 person_id, 'Bob' first_name, 'Smith' last_name FROM dual
UNION ALL
SELECT 3 person_id, 'Charles' first_name, 'Walker' last_name FROM dual
UNION ALL
SELECT 4 person_id, 'Dave' first_name, 'Simpson' last_name FROM dual
UNION ALL
SELECT 5 person_id, 'Elaine' first_name, 'Andrews' last_name FROM dual)
select person_id, first_name, last_name, ri1, ri2
from t1
model dimension by (
Row_number () Over (Order By person_id) Idx,
Row_number() Over (Order By dbms_random.value(1, 10000) ) ri1,
Row_number() Over (Order By dbms_random.value(1, 200)) ri2)
measures (person_id, first_name, last_name)
rules sequential order (
first_name[idx, ri1, ri2] order by idx = MIN(first_name)[cv(ri1), ANY, ANY],
last_name[idx, ri1, ri2] order by idx = MIN(last_name)[cv(ri2), ANY, ANY]
)
order by person_id;

The mentioned link howeber means theres no hope for production use :

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:3181424400346795479

sort of randomly confused

Duke Ganote, February 10, 2017 - 3:34 pm UTC

I've skimmed through the AskTom link referenced by Racer I. However, I don't see anything that explains why the nn and jj values are identical:

WITH e AS (
SELECT 0 empno, '' ename, '' job     FROM DUAL WHERE 1=0 UNION ALL
SELECT 7369, 'SMITH'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7499, 'ALLEN'   , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7521, 'WARD'    , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7566, 'JONES'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7654, 'MARTIN'  , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7698, 'BLAKE'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7782, 'CLARK'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7788, 'SCOTT'   , 'ANALYST'   FROM DUAL UNION ALL
SELECT 7839, 'KING'    , 'PRESIDENT' FROM DUAL UNION ALL
SELECT 7844, 'TURNER'  , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7876, 'ADAMS'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7900, 'JAMES'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7902, 'FORD'    , 'ANALYST'   FROM DUAL UNION ALL
SELECT 7934, 'MILLER'  , 'CLERK'     FROM DUAL
) -- e
, r AS (
SELECT empno
     , ROW_NUMBER()OVER(ORDER BY empno) e#
     , ename
     , job
     , dbms_random.value n#
     , dbms_random.value j#
  FROM e
) -- r
SELECT empno
     , e#
     , ename
     , job
     , n#
     , j#
     , ROW_NUMBER() OVER(ORDER BY n#) as nn
     , ROW_NUMBER() OVER(ORDER BY j#) as jj
  FROM r
 ORDER BY e#
:55 wpp\dganote> /

EMPNO         E# ENAME  JOB               N#         J#         NN         JJ
----- ---------- ------ --------- ---------- ---------- ---------- ----------
 7369          1 SMITH  CLERK     .725508671 .205255786         11         11
 7499          2 ALLEN  SALESMAN  .837936901 .107661642         14         14
 7521          3 WARD   SALESMAN  .425336683 .501335285          8          8
 7566          4 JONES  MANAGER   .032632158 .942861435          1          1
 7654          5 MARTIN SALESMAN  .259570808 .625548861          4          4
 7698          6 BLAKE  MANAGER   .731231913 .559631394         12         12
 7782          7 CLARK  MANAGER   .288009925  .48340873          6          6
 7788          8 SCOTT  ANALYST   .465570095 .561542219          9          9
 7839          9 KING   PRESIDENT .267650141 .997561229          5          5
 7844         10 TURNER SALESMAN   .15307065 .588990867          3          3
 7876         11 ADAMS  CLERK     .780813888 .355056171         13         13
 7900         12 JAMES  CLERK     .360827897 .839259406          7          7
 7902         13 FORD   ANALYST   .120771687  .82514804          2          2
 7934         14 MILLER CLERK     .613049156 .487918853         10         10


I tried it with:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
and
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Connor McDonald
February 10, 2017 - 5:08 pm UTC

Most probably an optimization.

"Hey, that function is repeated...I'll just call it once and use the value twice"

If I make a slight alteration so they are different, you can see the effect

SQL> WITH e AS (
  2  SELECT 0 empno, '' ename, '' job     FROM DUAL WHERE 1=0 UNION ALL
  3  SELECT 7369, 'SMITH'   , 'CLERK'     FROM DUAL UNION ALL
  4  SELECT 7499, 'ALLEN'   , 'SALESMAN'  FROM DUAL UNION ALL
  5  SELECT 7521, 'WARD'    , 'SALESMAN'  FROM DUAL UNION ALL
  6  SELECT 7566, 'JONES'   , 'MANAGER'   FROM DUAL UNION ALL
  7  SELECT 7654, 'MARTIN'  , 'SALESMAN'  FROM DUAL UNION ALL
  8  SELECT 7698, 'BLAKE'   , 'MANAGER'   FROM DUAL UNION ALL
  9  SELECT 7782, 'CLARK'   , 'MANAGER'   FROM DUAL UNION ALL
 10  SELECT 7788, 'SCOTT'   , 'ANALYST'   FROM DUAL UNION ALL
 11  SELECT 7839, 'KING'    , 'PRESIDENT' FROM DUAL UNION ALL
 12  SELECT 7844, 'TURNER'  , 'SALESMAN'  FROM DUAL UNION ALL
 13  SELECT 7876, 'ADAMS'   , 'CLERK'     FROM DUAL UNION ALL
 14  SELECT 7900, 'JAMES'   , 'CLERK'     FROM DUAL UNION ALL
 15  SELECT 7902, 'FORD'    , 'ANALYST'   FROM DUAL UNION ALL
 16  SELECT 7934, 'MILLER'  , 'CLERK'     FROM DUAL
 17  ) -- e
 18  , r AS (
 19  SELECT empno
 20       , ROW_NUMBER()OVER(ORDER BY empno) e#
 21       , ename
 22       , job
 23       , dbms_random.value(0,1) n#
 24       , dbms_random.value(0.01,1.01) j#
 25    FROM e
 26  ) -- r
 27  SELECT empno
 28       , e#
 29       , ename
 30       , job
 31       , n#
 32       , j#
 33       , ROW_NUMBER() OVER(ORDER BY n#) as nn
 34       , ROW_NUMBER() OVER(ORDER BY j#) as jj
 35    FROM r
 36   ORDER BY e#
 37   /

     EMPNO         E# ENAME  JOB               N#         J#         NN         JJ
---------- ---------- ------ --------- ---------- ---------- ---------- ----------
      7369          1 SMITH  CLERK     .982336159  .18137263         14          2
      7499          2 ALLEN  SALESMAN  .732643858 .748121837         11         10
      7521          3 WARD   SALESMAN  .311485519 .821971397          4         11
      7566          4 JONES  MANAGER   .268279418 .242935577          3          4
      7654          5 MARTIN SALESMAN   .07479744 .614140745          1          8
      7698          6 BLAKE  MANAGER   .075123385 .999267552          2         14
      7782          7 CLARK  MANAGER   .495880039 .188252118          7          3
      7788          8 SCOTT  ANALYST   .440915261 .688732452          6          9
      7839          9 KING   PRESIDENT .692111831 .055156106         10          1
      7844         10 TURNER SALESMAN  .776020945 .994664663         12         13
      7876         11 ADAMS  CLERK     .656738426 .909354769          9         12
      7900         12 JAMES  CLERK       .6462918 .514383296          8          6
      7902         13 FORD   ANALYST   .410987957 .305183593          5          5
      7934         14 MILLER CLERK     .926030743 .610280442         13          7


Living in a MATERIALIZE world

Duke Ganote, February 10, 2017 - 4:23 pm UTC

I suppose you can give the optimizer a hint:

 WITH e AS (
 SELECT 0 empno, '' ename, '' job     FROM DUAL WHERE 1=0 UNION ALL
 SELECT 7369, 'SMITH'   , 'CLERK'     FROM DUAL UNION ALL
 SELECT 7499, 'ALLEN'   , 'SALESMAN'  FROM DUAL UNION ALL
 SELECT 7521, 'WARD'    , 'SALESMAN'  FROM DUAL UNION ALL
 SELECT 7566, 'JONES'   , 'MANAGER'   FROM DUAL UNION ALL
 SELECT 7654, 'MARTIN'  , 'SALESMAN'  FROM DUAL UNION ALL
 SELECT 7698, 'BLAKE'   , 'MANAGER'   FROM DUAL UNION ALL
 SELECT 7782, 'CLARK'   , 'MANAGER'   FROM DUAL UNION ALL
 SELECT 7788, 'SCOTT'   , 'ANALYST'   FROM DUAL UNION ALL
 SELECT 7839, 'KING'    , 'PRESIDENT' FROM DUAL UNION ALL
 SELECT 7844, 'TURNER'  , 'SALESMAN'  FROM DUAL UNION ALL
 SELECT 7876, 'ADAMS'   , 'CLERK'     FROM DUAL UNION ALL
 SELECT 7900, 'JAMES'   , 'CLERK'     FROM DUAL UNION ALL
 SELECT 7902, 'FORD'    , 'ANALYST'   FROM DUAL UNION ALL
 SELECT 7934, 'MILLER'  , 'CLERK'     FROM DUAL
 ) -- e
 , n AS (
 SELECT /*+ MATERIALIZE */ empno
      , ename
      , job
      , ROW_NUMBER()OVER(ORDER BY empno) e#
      , ROW_NUMBER()OVER(ORDER BY dbms_random.value) n#
   FROM e
 ) -- r
 , j AS (
 SELECT empno
      , ename
      , job
      , e#
      , n#
      , ROW_NUMBER()OVER(ORDER BY dbms_random.value) j#
   FROM n
 )
 SELECT empno
      , ename
      , job
      , CASE WHEN e#-n# < 0
             THEN LEAD(ename,ABS(e#-n#))OVER
                    (ORDER BY e#)
             ELSE LAG(ename,ABS(e#-n#))OVER
                    (ORDER BY e#)
              END as rname
      , CASE WHEN e#-j# < 0
             THEN LEAD(job,ABS(e#-j#))OVER
                    (ORDER BY e#)
             ELSE LAG(job,ABS(e#-j#))OVER
                    (ORDER BY e#)
              END as rjob
      , e#
      , n#
      , j#
   FROM j
  ORDER BY e#
1:04 wpp\dganote> /

 EMPNO ENAME  JOB       RNAME  RJOB       E#  N#  J#
------ ------ --------- ------ --------- --- --- ---
  7369 SMITH  CLERK     MARTIN CLERK       1   5  12
  7499 ALLEN  SALESMAN  CLARK  SALESMAN    2   7   2
  7521 WARD   SALESMAN  BLAKE  CLERK       3   6   1
  7566 JONES  MANAGER   WARD   ANALYST     4   3  13
  7654 MARTIN SALESMAN  MILLER CLERK       5  14  11
  7698 BLAKE  MANAGER   ADAMS  MANAGER     6  11   7
  7782 CLARK  MANAGER   JONES  SALESMAN    7   4   5
  7788 SCOTT  ANALYST   KING   ANALYST     8   9   8
  7839 KING   PRESIDENT ALLEN  CLERK       9   2  14
  7844 TURNER SALESMAN  FORD   PRESIDENT  10  13   9
  7876 ADAMS  CLERK     SCOTT  SALESMAN   11   8   3
  7900 JAMES  CLERK     SMITH  MANAGER    12   1   4
  7902 FORD   ANALYST   JAMES  SALESMAN   13  12  10
  7934 MILLER CLERK     TURNER MANAGER    14  10   6

Tricking the Optimizer; Thanking the Connor

Duke Ganote, February 10, 2017 - 5:38 pm UTC

Thank you. I suppose there are always bits of code that, as the link suggests, implicitly rely on a particular version of the optimizer. One can expect testing and/or surprises when upgrading...

WITH e AS (
SELECT 0 empno, '' ename, '' job     FROM DUAL WHERE 1=0 UNIO
SELECT 7369, 'SMITH'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7499, 'ALLEN'   , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7521, 'WARD'    , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7566, 'JONES'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7654, 'MARTIN'  , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7698, 'BLAKE'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7782, 'CLARK'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7788, 'SCOTT'   , 'ANALYST'   FROM DUAL UNION ALL
SELECT 7839, 'KING'    , 'PRESIDENT' FROM DUAL UNION ALL
SELECT 7844, 'TURNER'  , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7876, 'ADAMS'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7900, 'JAMES'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7902, 'FORD'    , 'ANALYST'   FROM DUAL UNION ALL
SELECT 7934, 'MILLER'  , 'CLERK'     FROM DUAL
) -- e
, n AS (
SELECT /*+ MATERIALIZE */ empno
     , ename
     , job
     , ROW_NUMBER()OVER(ORDER BY empno) e#
     , ROW_NUMBER()OVER(ORDER BY dbms_random.value(0,2)/2) n#
     , ROW_NUMBER()OVER(ORDER BY dbms_random.value(0,1)) j#
  FROM e
) -- r
SELECT empno
     , ename
     , job
     , CASE WHEN e#-n# < 0
            THEN LEAD(ename,ABS(e#-n#))OVER
                   (ORDER BY e#)
            ELSE LAG(ename,ABS(e#-n#))OVER
                   (ORDER BY e#)
             END as rname
     , CASE WHEN e#-j# < 0
            THEN LEAD(job,ABS(e#-j#))OVER
                   (ORDER BY e#)
            ELSE LAG(job,ABS(e#-j#))OVER
                   (ORDER BY e#)
             END as rjob
     , e#
     , n#
     , j#
     , case e# when n# then 'Y' end en#
     , case e# when j# then 'Y' end ej#
  FROM n
 ORDER BY e#
:49 wpp\dganote> /

EMPNO ENAME  JOB       RNAME  RJOB       E#  N#  J# E E
----- ------ --------- ------ --------- --- --- --- - -
 7369 SMITH  CLERK     KING   MANAGER     1   9   7
 7499 ALLEN  SALESMAN  JAMES  CLERK       2  12   1
 7521 WARD   SALESMAN  ALLEN  ANALYST     3   2   8
 7566 JONES  MANAGER   SCOTT  SALESMAN    4   8   2
 7654 MARTIN SALESMAN  WARD   SALESMAN    5   3   5   Y
 7698 BLAKE  MANAGER   BLAKE  CLERK       6   6  12 Y
 7782 CLARK  MANAGER   ADAMS  MANAGER     7  11   6
 7788 SCOTT  ANALYST   FORD   CLERK       8  13  11
 7839 KING   PRESIDENT MARTIN CLERK       9   5  14
 7844 TURNER SALESMAN  SMITH  SALESMAN   10   1  10   Y
 7876 ADAMS  CLERK     TURNER MANAGER    11  10   4
 7900 JAMES  CLERK     MILLER ANALYST    12  14  13
 7902 FORD   ANALYST   CLARK  PRESIDENT  13   7   9
 7934 MILLER CLERK     JONES  SALESMAN   14   4   3

How random?

John Keymer, February 13, 2017 - 8:31 am UTC

Racer I, thanks for the input. I tried the method you posted:


WITH 
t1 AS ( 
SELECT 1 person_id, 'Alice' first_name, 'Jones' last_name FROM dual 
UNION ALL 
SELECT 2 person_id, 'Bob' first_name, 'Smith' last_name FROM dual 
UNION ALL 
SELECT 3 person_id, 'Charles' first_name, 'Walker' last_name FROM dual 
UNION ALL 
SELECT 4 person_id, 'Dave' first_name, 'Simpson' last_name FROM dual 
UNION ALL 
SELECT 5 person_id, 'Elaine' first_name, 'Andrews' last_name FROM dual) 
SELECT person_id, 
MAX(first_name) OVER (ORDER BY dbms_random.value * dbms_random.value) first_name, 
MAX(last_name) OVER (ORDER BY dbms_random.value * dbms_random.value) last_name 
from t1 
order by 1 


but was surprised that the first time I ran it, I got the following:

1 1 Elaine Walker
2 2 Elaine Walker
3 3 Elaine Walker
4 4 Elaine Walker
5 5 Elaine Andrews


now I know theoretically that with a set of 5 random numbers that is more than possible, however over multiple runs I was seeing similar results. I.e. just two runs later I got this:

1 1 Charles Walker
2 2 Charles Walker
3 3 Charles Walker
4 4 Elaine Walker
5 5 Elaine Walker


So I did this:

Create Function xxjktst Return Number
Is
  n Number;
Begin
  n := Dbms_Random.Value;
  Dbms_Application_Info.Set_Client_Info(Userenv('CLIENT_INFO') || '~' || To_Char(Round(n,2)));
  Return n;
End;
/

Exec Dbms_Application_Info.Set_Client_Info(Null);


Then ran:

WITH 
t1 AS ( 
SELECT 1 person_id, 'Alice' first_name, 'Jones' last_name FROM dual 
UNION ALL 
SELECT 2 person_id, 'Bob' first_name, 'Smith' last_name FROM dual 
UNION ALL 
SELECT 3 person_id, 'Charles' first_name, 'Walker' last_name FROM dual 
UNION ALL 
SELECT 4 person_id, 'Dave' first_name, 'Simpson' last_name FROM dual 
UNION ALL 
SELECT 5 person_id, 'Elaine' first_name, 'Andrews' last_name FROM dual) 
SELECT person_id, 
MAX(first_name) OVER (ORDER BY xxjktst * xxjktst) first_name, 
MAX(last_name) OVER (ORDER BY xxjktst * xxjktst) last_name 
from t1 
order by 1 ;


which gave:

1 1 Elaine Walker
2 2 Elaine Walker
3 3 Charles Walker
4 4 Dave Walker
5 5 Elaine Walker


Select Userenv('client_info') n From Dual;


~.84~.34~.52~.66~.48~.14~.9~.11~.86~.13


First thing I notice is that the function is only executed twice per row, rather than four times per row. However secondly, the numbers are all different. So perhaps given that rows don't have an explicit order anyway, there is a side effect of calling the function that makes particular rows more likely to be returned as the first with a non-deterministic ordering?

1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2 PL/SQL Release 11.2.0.3.0 - Production
3 CORE 11.2.0.3.0 Production
4 TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
5 NLSRTL Version 11.2.0.3.0 - Production

Semi-Random

Racer I., February 13, 2017 - 10:32 am UTC

Hi John,

possible reason :

From Oracle-Doc :
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

So I guess it takes row by row (by some internal order) and picks the lowest name from the rows so far. Only when a new row is lower can the value change. If the lowest is picked first it never changes.

If you use UNBOUNDED PRECEDING/FOLLOWING the result becomes deterministic :(

You can step by step through an output to follow the logic (and guess the original order from it).

regards,
Racer I.

Shuffling vs Randomly Ordering

Duke Ganote, February 13, 2017 - 3:35 pm UTC

If you assign each record a unique sequential integer, then "shuffling" or "randomly ordering" follow easily (in other words, drawing without or with replacement, respectively).

The trick was mapping back to the values. Ms Mentzel's question on NTH_VALUE a few days back was due to the two of us considering methods for mapping the random/shuffled integers back to the values
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9533175800346770000


The following query demonstrates an approach to randomly drawing names and shuffling names:

exec dbms_random.seed(0);
WITH
t1 AS (
SELECT 0 empno, '' ename, '' job     FROM DUAL WHERE 1=0 UNION ALL
SELECT 7369, 'SMITH'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7499, 'ALLEN'   , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7521, 'WARD'    , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7566, 'JONES'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7654, 'MARTIN'  , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7698, 'BLAKE'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7782, 'CLARK'   , 'MANAGER'   FROM DUAL UNION ALL
SELECT 7788, 'SCOTT'   , 'ANALYST'   FROM DUAL UNION ALL
SELECT 7839, 'KING'    , 'PRESIDENT' FROM DUAL UNION ALL
SELECT 7844, 'TURNER'  , 'SALESMAN'  FROM DUAL UNION ALL
SELECT 7876, 'ADAMS'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7900, 'JAMES'   , 'CLERK'     FROM DUAL UNION ALL
SELECT 7902, 'FORD'    , 'ANALYST'   FROM DUAL UNION ALL
SELECT 7934, 'MILLER'  , 'CLERK'     FROM DUAL )
, rnd AS (
SELECT t1.*
     , ROW_NUMBER()OVER(ORDER BY empno) AS emp#
  -- RANDOM DRAWS WITH REPLACEMENT
     , ROW_NUMBER()OVER(ORDER BY empno)
      -round(dbms_random.value(1,count(*)over())) as rnd#1
     , ROW_NUMBER()OVER(ORDER BY empno)
      -round(dbms_random.value(2*1,2*count(*)over())/2) as rnd#2
  -- SHUFFLING (RANDOM DRAWS WITHOUT REPLACEMENT)
     , ROW_NUMBER()OVER(ORDER BY empno)
      -ROW_NUMBER()OVER(ORDER BY dbms_random.value(0,1)/1) as shf#1
     , ROW_NUMBER()OVER(ORDER BY empno)
      -ROW_NUMBER()OVER(ORDER BY dbms_random.value(0,2)/2) as shf#2
  from t1
)
SELECT rnd.*
  -- random names
     , CASE WHEN rnd#1 < 0
            THEN LEAD(ename,ABS(rnd#1))OVER
                   (ORDER BY emp#)
            ELSE LAG(ename,ABS(rnd#1))OVER
                   (ORDER BY emp#)
             END as r#1_nm
     , CASE WHEN rnd#2 < 0
              THEN LEAD(ename,ABS(rnd#2))OVER
                   (ORDER BY emp#)
            ELSE LAG(ename,ABS(rnd#2))OVER
                   (ORDER BY emp#)
             END as r#2_nm
  -- shuffled names
     , CASE WHEN shf#1 < 0
            THEN LEAD(ename,ABS(shf#1))OVER
                   (ORDER BY emp#)
            ELSE LAG(ename,ABS(shf#1))OVER
                   (ORDER BY emp#)
             END as s#1_nm
     , CASE WHEN shf#2 < 0
            THEN LEAD(ename,ABS(shf#2))OVER
                   (ORDER BY emp#)
            ELSE LAG(ename,ABS(shf#2))OVER
                   (ORDER BY emp#)
             END as s#2_nm
  from rnd
order by 1
/

EMPNO ENAME  JOB        EMP#  RND#1  RND#2  SHF#1  SHF#2 R#1_NM R#2_NM S#1_NM S#2_NM
----- ------ --------- ----- ------ ------ ------ ------ ------ ------ ------ ------
 7369 SMITH  CLERK         1     -3     -9      0    -11 JONES  TURNER SMITH  JAMES
 7499 ALLEN  SALESMAN      2      1    -10    -10      1 SMITH  JAMES  JAMES  SMITH
 7521 WARD   SALESMAN      3    -11      2     -1     -2 MILLER SMITH  JONES  MARTIN
 7566 JONES  MANAGER       4      1      2      1     -2 WARD   ALLEN  WARD   BLAKE
 7654 MARTIN SALESMAN      5     -1      2     -1      1 BLAKE  WARD   BLAKE  JONES
 7698 BLAKE  MANAGER       6      4      2      4     -2 ALLEN  JONES  ALLEN  SCOTT
 7782 CLARK  MANAGER       7     -5      1     -1     -3 JAMES  BLAKE  SCOTT  TURNER
 7788 SCOTT  ANALYST       8      2      3     -6     -6 BLAKE  MARTIN MILLER MILLER
 7839 KING   PRESIDENT     9     -2      7     -4      2 ADAMS  ALLEN  FORD   CLARK
 7844 TURNER SALESMAN     10      0      5     -1      8 TURNER MARTIN ADAMS  ALLEN
 7876 ADAMS  CLERK        11      8      3      4      0 WARD   SCOTT  CLARK  ADAMS
 7900 JAMES  CLERK        12      2     -1      7     -1 TURNER FORD   MARTIN FORD
 7902 FORD   ANALYST      13      7     11      4     10 BLAKE  ALLEN  KING   WARD
 7934 MILLER CLERK        14     12     11      4      5 ALLEN  WARD   TURNER KING

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.