Populate columns with random numbers

Quote:

---

Originally posted by DaPi
Jeff, Jurij,

this is part of my problem: PL/SQL supplied packages doc for 8i & 9i doesn't mention dbms_random.value - where is it documented?

---

In the database itself! :p

No, seriously, DBMS_RANDOM is one of those packages where the package specification (PL/SQL) in the dictionary is actually quite decently documented. Check it out.

And BTW, DBMS_RANDOM is one of the very few (if not even the only one!) DBMS_* packages that doesn't have its body wrapped! So you can check the source code of the body as well. :cool:

Ah, RTFDBMS !

Quote:

---

Originally posted by DaPi
Ah, RTFDBMS !

---

Exactly! ROTFLOL

Kudos to PADDERS.
However, his SQL will not work in 9i.

Here is the one that worked in 9i.

PHP Code:

---

SQL> L
  1  select N1,
  2         (case WHEN N2 < N1 then N2
  3              WHEN N3 < N1 then N3
  4              WHEN N4 < N1 then N4
  5              WHEN N5 < N1 then N5
  6              WHEN N6 < N1 then N6
  7              WHEN N7 < N1 then N7
  8              WHEN N8 < N1 then N8
  9              ELSE 0
 10         end ) NEW
 11  from ( select /*+ NO_MERGE */ N1,
 12                trunc(dbms_random.value(0,6)) N2,
 13                trunc(dbms_random.value(0,6)) N3,
 14                trunc(dbms_random.value(0,6)) N4,
 15                trunc(dbms_random.value(0,6)) N5,
 16                trunc(dbms_random.value(0,6)) N6,
 17                trunc(dbms_random.value(0,6)) N7,
 18                trunc(dbms_random.value(0,6)) N8
 19           from ( select
 20                   trunc(dbms_random.value(1, 7)) N1
 21                   from dual )
 22        )
 23*
SQL> /

        N1        NEW
---------- ----------
         5          2 


---

Warning. The NO_MERGE hint is absolutely necessary otherwise, you will get a wrong result.

Tamil

Quote:

---

Originally posted by tamilselvan
Kudos to PADDERS.
However, his SQL will not work in 9i.

---

What are you talking about?

Code:

---

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> SELECT n, TRUNC (dbms_random.value (0, n)) m
  2  FROM
  3  (SELECT TRUNC (dbms_random.value (1, 7)) n FROM scott.emp);

        N          M
---------- ----------
        4          2
        4          0
        2          1
        5          4
        6          0
        3          1
        3          1
        2          0
        5          0
        5          1
        5          4
        6          3
        3          1
        3          1

14 rows selected.

SQL>

---

I am talking about the DIMENSION used in 10G.

Tamil

Tamil,

That DIMENSION word in padder's example is just a part of the MODEL clause - and it's a model clause that is not supported prior to 10g. And I think I've made that part clear enough before with:

Quote:

---

Originally posted by jmodic
Of course the specific implementation of
{your_row_source_here} that padders used in the second query could not work prior to 10g, since MODEL clause is not available in pre-10g. But that is not essential for your problem, you simply replace the {your_row_source_here} with your table name.

---

Besides, what you used in your example has absolutely nothing to do with the functionality of MODEL, so how can you say it should be used as a 9i replacement for what padders have demonstrated for 10g using MODEL? The SQL you provided is just a silly example of how one can write an overcomplicated 22-line query (of course not forgetting to use a mandatory!!!! hint) when it can be written in a trivial 2-line statement.

So if anyone is ever tempted to use your suggeted half-screen long SQL, I urge him/her to rather use plain simple statement that padders have sugested in the first place and does exactly the same as yours:

Code:

---

SELECT n, TRUNC (dbms_random.value (0, n)) m
FROM (SELECT TRUNC (dbms_random.value (1, 7)) n FROM dual);

---

=========
So if anyone is ever tempted to use your suggeted half-screen long SQL, I urge him/her to rather use plain simple statement that padders have sugested in the first place and does exactly the same as yours:

code:-----------------------------------------------------
SELECT n, TRUNC (dbms_random.value (0, n)) m
FROM (SELECT TRUNC (dbms_random.value (1, 7)) n FROM dual);
---------------------------------------------------------------------===
Modic, Did you test the code before you posted here?

See the output:

PHP Code:

---

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> spool x.log
SQL> SELECT n, TRUNC (dbms_random.value (0, n)) m
FROM (SELECT TRUNC (dbms_random.value (1, 7)) n FROM dual);
  2
         N          M
---------- ----------
         1          0

SQL> /

         N          M
---------- ----------
         4          5

SQL> spool off 


---

The original poster wanted M should be less than N.

Whether I write 2 lines of code or 20 lines of code , it does not matter, ultimately the result should be correct.
I hope you got the message.

Tamil

SELECT n, TRUNC (DBMS_RANDOM.VALUE (0, n)) m
FROM (SELECT TRUNC (DBMS_RANDOM.VALUE (1, 7)) n
FROM customer_view_groups)


N M
--------- ---------
5 1
4 2
6 0
3 0
1 3
3 1
3 2
2 5
4 2
3 1


And here's me keeping quiet because I thought I'd missed the whole point. Thanks Tamil.

Opinions as to solution aside Tamil raises an important point - both solutions involve a risk of getting an incorrect answer here because of view merging.

If anyone is unclear about what is happening here, the optimizer is (semi-educated speculation follows) re-writing this SQL to remove the in-line view by substituting for n in the outer view.

In other words we have changed from this...

Code:

---

Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT n, TRUNC (DBMS_RANDOM.VALUE (0, n)) m
  2  FROM  (SELECT TRUNC (DBMS_RANDOM.VALUE (1, 7)) n
  3        FROM  emp);

        N          M
---------- ----------
        6          0
        2          0
        4          0
        4          1
        5          4
        4          0
        4          2
        1          0
        2          0
        2          0
        4          0
        2          0
        4          3
        2          0
       
14 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=14 Bytes=182)
  1    0  VIEW (Cost=1 Card=14 Bytes=182)
  2    1    INDEX (FULL SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost=1 Card=14)

SQL>

---

...to something similar to this...

Code:

---

SQL> SELECT TRUNC (DBMS_RANDOM.VALUE (1, 7)) n,
  2          TRUNC (DBMS_RANDOM.VALUE (0,
  3      TRUNC (DBMS_RANDOM.VALUE (1, 7)))) m
  4  FROM  emp;

        N          M
---------- ----------
        1          2 <--
        5          1
        5          0
        3          0
        1          1
        4          1
        1          1
        5          2
        4          5 <--
        1          2 <--
        4          5 <--
        1          0
        3          0
        6          1

14 rows selected.

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=14)
  1    0  INDEX (FULL SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost=1 Card=14)
 
SQL>

---

...which essentially means the value of m is no longer derived from n and hence we get errors.

It is interesting to note from the first example that the view does not appear to be merged by default in 10g (i.e. I got lucky) - whether this is an artefact or evidence that the optimizer is actually being smart enough to know the difference I do not know (no doubt the former).

In any case as Tamil also points out we can prevent the view merging, something generally done by the addition of NO_MERGE hint or ROWNUM to the inner query.