Printable View
Hi All,
Gonna research this this morning, but someone may have a 'quick and ready' solution for me.........
Need to populate 2 new columns on a demo database on the same table. The columns are NUMBER_OF_TIMES_MAILED and NUMBER_OF_TIMES_RESPONDED.
MAILED column needs to be a random number between 1 and 6. RESPONDED column needs to be random, but less than MAILED.
Any input would be appreciated. :)
dbms_random
(I suspect the 8i & 9i doc may not be complete)
Something like this perhaps...
for example...Code:SELECT n, TRUNC (DBMS_RANDOM.VALUE (0, n)) m
FROM (SELECT TRUNC (DBMS_RANDOM.VALUE (1, 7)) n
FROM {your_row_source_here})
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 dual
4 MODEL DIMENSION BY (0 x) MEASURES (0 y)
5 RULES ITERATE (10) (y [iteration_number] = 0));
N M
---------- ----------
4 3
2 0
3 0
1 0
3 2
2 1
2 1
4 3
3 0
3 1
10 rows selected.
SQL>
I'm impressed. Thanks for the input padders.
Of course, I have no idea what the code is doing.
Does this only apply to 10g, as unfortunately we're productionised on 9i ??
That's why they have this wonderful thing called documentation!
c'mon. I'm swamped with building the flamin' demo database (last minute request from sales), on top of the daily fire fighting, and answering stupid questions from the clients.
Gimme a break with the RTFM jibe. I posted in good faith, looking for a little help. Can you answer my question?
It should work with any release that has DBMS_RANDOM package available (at least from 8i on, I belive).Quote:
Originally posted by Horace
Does this only apply to 10g, as unfortunately we're productionised on 9i ??
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.
http://www.click-smilies.de/sammlung...smiley-019.gifQuote:
Originally posted by Horace
c'mon. I'm swamped with building the flamin' demo database (last minute request from sales), on top of the daily fire fighting, and answering stupid questions from the clients.
Gimme a break with the RTFM jibe. I posted in good faith, looking for a little help. Can you answer my question?
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?
Very funny Jeff, but ultimately useless........
In the database itself! :pQuote:
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?
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 !
Exactly! ROTFLOLQuote:
Originally posted by DaPi
Ah, RTFDBMS !
Kudos to PADDERS.
However, his SQL will not work in 9i.
Here is the one that worked in 9i.
Warning. The NO_MERGE hint is absolutely necessary otherwise, you will get a wrong result.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
Tamil
What are you talking about?Quote:
Originally posted by tamilselvan
Kudos to PADDERS.
However, his SQL will not work in 9i.
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:
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.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.
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:
The original poster wanted M should be less than N.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
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...
...to something similar to 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>
...which essentially means the value of m is no longer derived from n and hence we get errors.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>
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.
If the optimiser produces the wrong answer, I'd call that a bug.
Am I being naïve?
PADDERS, You got it.
I don't like to write SQL statements using EMP, USER_OBJECTS etc. Some times we may get correct result, some times not.
Tamil
> Am I being naïve?
It's not an unreasonable expectation. Have a read of Johnathan Gennick's Subquery Madness (not to mention the articles it links to).
http://five.pairlist.net/pipermail/o...04/000012.html
Thanks padders - got it.
Yes, I've had the problem where filtering "invalid" rows fails to work, Ã* la Gennick.
The problem here is that dbms_random is not a deterministic** function (just as one requires for FBI's).
(** that's perhaps not the right word ****).
(**** it is - see create function)
That sounds like a philisophical statement.Quote:
Originally posted by DaPi
The problem here is that dbms_random is not a deterministic
If dbms_random was deterministic would it still be random?
You must be kidding, right? Did I test it? Well, not only that I tested it, I also specified where I tested it in my post together with the results. I specified the release I was testing it against, you didn't. You simply claimed "It doesn't work with 9i", and that raised my eyebrows, as I realy couldn't see any reason why it shouldn't work with 9i. So I tested it on 9.0.1 (AFAIK this release falls into 9i category, doesn't it) and it worked. And it still works, 100%. I didn't test it only on scott.emp, I tested it on various small and large tables, and it consistently returns correct resutlts - M is allways less than N! Noone, not the original poster, nor you nor anyone else ever mentioned the exact release where the query should actualy be used - all it was said was that "it doesn't work on 9i". So I used the first 9i release that I found running on my PC and it just happened that it was 9.0.1. And I tested it on it, and I demonstrated that it works and I prooved you vere wrong. So much about testing-before-posting from my side.Quote:
Originally posted by tamilselvan
Modic, Did you test the code before you posted here?
Now you demonstrated it doesn't work correctly (which is something different from simply saying "it doesn't work" with the additional mumbling something about DIMENSION as being the reason....) on 9.2.0.5. So we an conclude that it does work in 9i and 10g, however it returns the incorrect results on 9Rel2 (at least on 9.2.0.5, don't know about other 9.2 patchsets), while it returns perfectly correct results in both 9.0 and 10.1.
DaPi pointed out the only correct conclusion from all this - it is a bug in 9.2.0.5 optimizer, which obviously is not the case with 9.0.1 nor with 10.1. It is a bug, period. The article from Jonathan Gennick (and further references on Asktom) could not dispute this fact. It is a bug.
Why? Because the case we are observing here in this thread doesn't return any error, it simply return the wrong result. The result it returns in 9.2 is in clear contradiction with the logic of the query. The logic of the query, as it is written, is 100% correct and noone can deny this. But the result that 9.2 returns does not follow the logic of the query, so by any criteria this is a bug.
On the other hand, the cases that Gennick and Tom are dealing with can not be caracterised as bugs. There oracle returns correct results with one execution path, while returns an error with another execution path (and btw, the error reported with those alternate execution plan were totaly legitimate for that particular execution path).
If oracle gives you a resultset that is obviously incorrect, than the case falls 100% into "the bug category". Someone here should perhaps report raise a TAR and report this as a bug. Good chances are that it has been reported allready (as it is obviously fixed in 10g), though.
Actually most random number generators ARE deterministic! (I don't have a db to hand, so I can't check the code of dbms_random). The result depends on the input parameters and a hidden number that is intialised with the SEED and updated with each call. This means you can have repeatable results for testing by using a fixed seed and non-repeatble results for real-life by setting the seed with (e.g.) sysdate.Quote:
Originally posted by gandolf989
That sounds like a philisophical statement.
If dbms_random was deterministic would it still be random?
Strictly speaking such output is chaotic, not random.
(I've now had some worrying thoughts about SYSDATE - a non-deterministic function . . . . . more later, when I've experimented.)
> which obviously is not the case with 9.0.1 nor with 10.1
The behaviour certainly appears consistently different. However just creating another non-deterministic function over DBMS_RANDOM.VALUE returns us to the same behaviour...
And how about this one?Code:Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE OR REPLACE FUNCTION random_value (
2 low IN NUMBER, high IN NUMBER)
3 RETURN NUMBER
4 IS
5 BEGIN
6 RETURN DBMS_RANDOM.VALUE (low, high);
7 END;
8 /
Function created.
SQL> SELECT n, TRUNC (random_value (0, n)) m
2 FROM (SELECT TRUNC (random_value (1, 7)) n
3 FROM emp);
N M
---------- ----------
5 1
3 1
3 0
4 1
6 1
4 0
1 1
2 0
3 1
1 2 <--
1 3
4 1
3 3
2 4 <--
14 rows selected.
SQL>
Looks good so far...Code:Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE OR REPLACE FUNCTION random_value (
2 low IN NUMBER, high IN NUMBER)
3 RETURN NUMBER
4 IS
5 BEGIN
6 RETURN DBMS_RANDOM.VALUE (low, high);
7 END;
8 /
Function created.
SQL> SELECT n, TRUNC (random_value (0, n)) m
2 FROM (SELECT TRUNC (random_value (1, 7)) n
3 FROM emp);
N M
---------- ----------
5 1
3 1
3 0
4 1
6 1
4 0
1 1
2 0
3 1
1 2
1 3
4 1
3 3
2 4
14 rows selected.
SQL> SELECT *
2 FROM (SELECT n, TRUNC (DBMS_RANDOM.VALUE (0, n)) m
3 FROM (SELECT TRUNC (DBMS_RANDOM.VALUE (1, 7)) n
4 FROM emp))
5 WHERE m > n;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL>
Ooops.Code:SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
N M
---------- ----------
4 3
1 0
3 2
3 1
1 0
2 1
5 0
2 1
4 0
1 0
4 3
4 0
5 4
3 2
14 rows selected.
SQL>
You are right. It apears that the bug is not (entirely) solved in 10g either. It looks that oracle's optimizer in certain circumstances still performs view merging when it clearly shouldn't. And your last example even shows that maybe view merging is not the culprit of the bug as it sometimes returns totaly wrong resultset even when view-merging does not take place.
In fact, very similar case as the one we discuss here is reported as a bug 3800878 (actualy a duplicate of bug 2391219, "INCONSISTENT RESULT WITH DBMS_RANDOM AS PREDICATE") on 10.1.0.2 on Metalink.
!Working.......
!Not working...
!Optimizer.....
!Bug...........
!Release.......
Hmmm, what else is pending to discuss?
Thanks PADDERS.
Tamil