As the function DBMS_RANDOM.VALUE (or the deprecated DBMS_RANDOM.RANDOM) is specified twice and is not deterministic, assuming that f=f is true when f is not null is a dubious rewrite.

And this used to work correctly in 10g...

select ...
  from t
 where f() = f()

select ...
  from (select f() F from t )
 where F=F

with data as (select f() F from dual)
select ...
  from t, data
 where F=F

select ...
  from t
 where (select f() from dual) = (select f() from dual)

select a, b, count(*) from t group by a,b;

How often SYSDATE is executed does not matter, it refers to the starttime at the execution of the query. It always return the same value, being executed once or multiple times.

With

select * from dual where sysdate=sysdate

In 10g it was

1 - filter(SYSDATE@!=SYSDATE@!)

And in 11g it is

1 - filter(SYSDATE@! IS NOT NULL)

Both are always true within one SQL statement.

The rewrite there is legal as the SYSDATE there returns always the same value.

But considering DBMS_RANDOM.VALUE=DBMS_RANDOM.VALUE to be true when DBMS_RANDOM.VALUE is not null is confusing enough to be considered as a bug.

> Is there is real world use of this, something "practical"?

Probably not, and therefore I will not open a bug

> select * from t where dbms_random.value(0,1) <= 0.10; 

I like this !

Could then 
select dbms_crypto.randomnumber, ename from emp
return only one distinct value? Scary...

Is "order by dbms_random.value" a special case?

Back when we were on 10g we had a process that needed to randomize lists of about 10 to 100 names. Sort of like a lottery. Randomize the list and then pick a random slot in the list as the "winner"

During testing "order by dbms_random.value" did a great job of randomizing the lists. We ran it across hundreds of thousands of test lists and analyzed the results.

The business need for the process went away, but the above discussion made me curious.

@Chuck : Definitely worth opening bug (if this would occur, which I doubt due to the number of application that will break).

Even if closed as a NOT-A-BUG, you will get a workaround.

Something like
<code>
select /*+optimizer_features_enable('10.1.0')*/ * from dual where dbms_random.value=dbms_random.value;

</code>

Well, that process and it's code is "in the attic" so to speak.
If the legislature brings it back I will re write the function.

Like Tom, I am not a fan of getting correct results by accident.

Tom, what would be a correct way of randomizing a list, preferably directly in sql?

so, the very commonly used order-by-clause
"order by dbms_random.value"
to randomize the sort order of a result set always worked more or less coincidentally ?

I just checked my two favourite google-custom-Oracle-searches
http://www.miracleoy.fi/search.html
and
http://sn.im/orclsearch
entering
"order by dbms_random.value"
and got tons of Oracle-Experts using this clause

for example Thomas Kyte:
http://www.oracle.com/technetwork/issue-archive/o14asktom-084433.html
"Generating Some Random Data"


seems to me that plenty of articles need to be rewritten

From my point of view, such treatment of non-deterministic and "not-builtin" functions is a major, if not a critical bug from Oracle side. All optimizations shall result in the semantically equivalent query - and if the function is neither deterministic nor returning the single value per query (and latter the optimizer can know only for builtins) any optimizations that change the number of calls break this equivalence, so must be prohibited - as they trade the results validity for the performance, which is absolutely unacceptable.

create table t ( x int, y int );
select * from t where x = f();

create index t_idx on t(x);

select * from t where x = f() and y = 5;

select * from t where x = f() 

select * from t where x = (select f() from dual)

That's true for the where clause - no doubt, but it is not true for select list, group by and order by clauses.
All of them are (or may be) executed after the row is included into the result set. So we may safely require that the function shall be called once and only once for each row.

Moreover, even for the where clause it may be reasonably required that the function shall be called at least once per each row considered as a "candidate" - including all rows found their way into the result set.
Just as if the function accept a rowid parameter.

. That's true for the where clause - no doubt, but it is not true for select list, group by and order by clauses.
...

why not?
Because we may firstly select the row (include it into the result set) and only then calculate the function in its select list, group by, order by (and optionally remember the result with the row for a later reuse).

how so? with view merging and other rewrite techniques - you quite simply cannot say that.

Why? Moreover, even if it is so (quite unlikely!) it only means that such techniques are invalid for this type of queries. Semantics cannot be traded for performance! No way.

... Moreover, even for the where clause it may be reasonably required that the function shall be called at least once per each row considered as a "candidate" - including all rows found their way into the result set.
Just as if the function accept a rowid parameter. ...

that is not even close to being reasonable. You would never be able to use an index as an access path - ever - if that were the case.

Again, why? I did not mean that each row in a table shall be considered as a "candidate", I meant "each row that was really assessed for inclusion" - namely, the conditions from where clause were evaluated against it.
Sure, depending on an execution plan number of calls to the function may change, it is OK. It is only required that function was called once for each row included in the result set (and likely many more times on the "thrown-out" rows).


There is no reason whatsoever that a function with no inputs cannot be considered a constant - none. Show me where there is - other than your opinion?
If it may be considered as a constant it means that it is deterministic - by the very definition of the deterministic function.
DETERMINISTIC Clause
Specify DETERMINISTIC to indicate that the function returns the same result value
whenever it is called with the same values for its arguments.

That said I always longed for a "statement-deterministic" clause that mark the function as deterministic inside statement (like SYSDATE).

"...please reference a specification for the language that says otherwise. ..."

ok, I see, that's a point
I didn't find any specification in Oracle documentation concerning the question "how often a PL/SQL-function used in sql is evaluated during execution of this sql"
(I "know" - probably it is documented - SYSDATE in one sql is always the SYSDATE when the execution started, I "know" - probably it is documented - about scalar subquery caching, but I don't know much else about it).

So, that's a bit easy for a software vendor: don't specify how things work, and nobody can complain in the future when they will work another way like expected.

This has a number of (ugly) consequences, for example:

- there is nothing like "code stability" in Oracle when I am using PL/SQL-functions within SQL. My code can (and so, most likely, will) break in the future. Good news for developers who look for not being redundant in the future, bad news for developers who want to deliver clean and robust code.

- I *have* to code more "procedural". I can't rely on smart SQL-constructs like for example "order by dbms_random.value" anymore, instead next time I need a randomly ordered result, I will have to code it procedurally ( just one example ). Sigh, I always was proud of being more clever than these procedural guys when it came to "thinking sets", now I see some of the limits

Thanks for answering my question anyway !

This is probably an illegal optimization that was introduced in Oracle8, and now it is a bit too late to expect to have

SELECT * FROM EMP WHERE DBMS_RANDOM.VALUE<.5

returning 50% of the rows.

Tom, it is GOOD TO KNOW how things works and how things breaks. It helps a lot

I won't change my mind that it is a misbehavior to cache something that is not cachable.

And estimating (DBMS_RANDOM.VALUE=DBMS_RANDOM.VALUE) to true will really look stupid for any programmer that will ever start working with Oracle

Your milage may vary

select *
from (select * from t order by x)
/

is 'dangerous', you might be lead to believe that it is the same as

select * from t order by x

but it isn't, it is the same as

select * from t


but this construct must work

SELECT * FROM
(SELECT empno FROM emp ORDER BY empno)
WHERE ROWNUM < 11;

because it is documented

top-N reporting works with

SELECT * FROM
   (SELECT empno FROM emp ORDER BY empno)
   WHERE ROWNUM < 11;

but you must not assume the result to be ordered by empno !

scalar subqueries achieve that to a large degree.
That's true. But with 2 caveats:
1. Readability is reduced.
2. For reasons unknown to me a usage of such subquieries often caused a drastic performance degradation, at least in 8i/9i.

will cause f(t.y) to be evaluated as few times as possible given the caching that takes place.

And who says that these results are suitable to be cached? This is purpose of the (proposed) clause to define their suitability.

"...please do go back and re-read, we covered that one, no you don't. If you make a function have unique inputs.... Then it cannot be constant. ..."

that may be true for 11.2
So, I should code (example)

create function rand(r in rowid) return number as begin return dbms_random.value; end rand;

and then

select * from (select * from <table> order by rand(rowid)) where rownum<=10

?

Probably, in V12 or 13 or 17, the optimizer discovers that rand does not depend on its input parameter and decides "hey, we optimize that and evaluate rand only once in this query".
Who tells me today it won't ?

I'm not really clear on how a non-deterministic function is particularly useful in sql. I'll refer to this example:

create table t ( x int, y int );
select * from t where x = f();

How could someone possibly determine whether f() gets called once for the entire query, or once for each row? Seems ambiguous, meaning the behavior is undefined (don't use it). Same in this case:

select x, f(), from t;

Again, once or once per row? Seems it could go either way.

I suppose you could "cheat" the optimizer by "lying" to it. Say we made f() deterministic, even if it is not (bad idea already, I know). Then, we give it a dummy parameter which has no affect on the output. Wouldn't the optimizer be forced to evaluate the function for every unique argument value? Something like this:

select x, f(dummy=>sys_guid()) from t;

Forgive my ignorance, but I'm having trouble wrapping my head around the whole idea of how non-deterministic functions make sense in a query.

I'm really eager to know who is this "Sokrates"? In Jonathan Lewis's blog, he is using a shortened URL which then redirects to a Wikipedia's page about the original, famous Socrates.
Anyway, good work Sokrates. This post has grown rapidly in size. Within 4 days, a lot of followups/comments. Keep it up Sokrates :-)

can you provide a working example for point #2, my experience has been opposite

Unfortunately, not now - I have met it long time ago (2003?) and do not remember all circumstances to model them.
Should add that I was very surprised because my previous experience was more in line with yours. If I would be able to reproduce this behavior in 11g (the only version to which I have an access right now) I would post here.

again we are right back to square one with someone ascribing to a non-procedural language some aspects of a procedural language.
How? I want declaratively describe the contract of my function - and such declarative description is perfectly in line with SQL philosophy.

As for a usability of non-deterministic functions in SQL - the same DBMS_RANDOM.value provides a perfect example of potentially usable non-deterministic function.

select *
  from (select f(), y from t where x = 10)
 where y > 10;

select f(), y from t where x = 10 and y > 10;

Hi Tom,


there is no input parameter to f() i.e. f(a) it may be executed once but if there is column pass to f() i.e f(col1) than it will executed for every row right?

so in the query below it will call 100 times

select *
from (select f(), y from t where x = 10)
where y > 10;


and 10 times if above query is transform as below

select f(), y from t where x = 10 and y > 10;

Thanks..

( to Parthiban Nagarajan from Coimbatore, India )

... is just an Oracle enthusiast
(since the first day I worked with this software in the spring of 1992), who likes to ask questions

Because I have such a long name in real life, I use "Sokrates" when asking questions
in order to save keystrokes :-)

I like questions starting with "how"
but my favourite questions start with "why"

( like the Sokrates on http://tinyurl.com/2xft2k )

there are 100 records such that x=10
there are 10 records such that y > 10 and x = 10

select *
from (select f(), y from t where x = 10)
where y > 10;


Semantically, the same as

select f(), y from t where x = 10 and y > 10;


How many times should f() be invoked.


At least ten - once for each row in the result.
And, strictly speaking, in the presence of non-deterministic function these 2 queries are not equivalent.

If f() returns arbitrary data for each call - then anything f() can return is acceptable and fine.

Definitely, not. E.g. I want to generate data with some distribution.

Give me an example of a function f() that returns a different value each time it is called and has real world meaning.
As said above DBMS_RANDOM.VALUE, ROWNUM(). DENSE_RANK(),...

why not 100? I was expecting that to be a stance - since
from (select f(), y from t where x = 10)
would find 100 rows.

100 would be nice - I said at least 10.


They are equivalent - if the function is non-deterministic (eg: like random), the output of any of them are equivalent - repeated runs of the query (even with the same plan) would not return the same result set. The entire THING is non-deterministic.
Probably yes, probably, no. Non-deterministic is not very good word. The real thing is it cannot be determined solely from parameters - but it may be very deterministic if an initial state is taken into account.


ROWNUM, ROW_NUMBER, DENSE_RANK do not count - for they are (assuming the order by is ordering by something UNIQUE) - deterministic. Remember - part of their inputs is the order by clause.
ORDER BY clause is not present in their parameter list :) So, strictly speaking, they are non-deterministic -in a sense of the DETERMINISTIC clause. They depend on the state.

Hi Tom,
A trick that we use to pass parameter to a dynamic SQL or VIEW is sys_context. So it is possible this function will be called for each row? Then it doesn't perform as good as bind variables.

Followup March 21, 2011 - 9am Central time zone:
As I said, I was expecting 100 to be your stance - not an option - based on your prior statements.

Yes, it would be even better - but it is mainly the matter of definition, that means how we specify semantics for such usages. From my point of view current Oracle semantics is unintuitive and unreasonable. It would be much more honest to prohibit to use any non-deterministic function in SQL.

Non-deterministic is the only way I can describe the output from a query using dbms_random in any way.

Sure, but my comments were not tied to DBMS_RANDOM. They addressed any function with results depending not only on its arguments.


Order by is part of their definition. It is part of their *inputs*. Deterministic is not so narrowly defined as to be dependent on formal named parameters.
Really? Then I have to quote a definition of the DETERMINISTIC clause again:
Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.

They do not depend on a state, they depend on their inputs.

They depend oh the state of the query processing, on the pre-history. You may consider it as an input of the state maintained outside - but anyhow it is not an argument.

You keep trying to force procedural constructs and rules on a language that proclaims as loud as it can "I am not procedural"
Which procedural constructs I want to force into SQL? Where you have noticed them? I am speaking exclusively about declarative constructs. Constructs that defines semantics of the outcome but not a way how this semantics will be achieved. Yes, I explained in which way an outcome is attainable - but it is just an example.


I am talking about the word DETERMINISTIC in general, in life, in the dictionary. I am not talking about the attribute deterministic as applied to a PLSQL function.
And I am talking exactly about this DETERMINISTIC clause - because its very existence proves that functions tagged with it should be treated differently then functions without such tagging. Otherwise this clause becomes senseless.

This has been an interesting discussion thread, but if someone needs that level of procedural control over a result set, why aren't they using pipelined functions?

In order for this function to be called once or one hundred times or five times or N times in fact:

select * from one_hundred_row_table where x = f();

F() does not need to be deterministic - it is not a requirement, it never has been, it never will be.

I argue that if f() is not deterministic (or "statement-deterministic") Oracle has no right to call its once only.


to_date( '2005', 'yyyy' ) is not deterministic, we only have to evaluate it once if we want to - and in fact - we do, we have and will...

It is what I call "statement-deterministic". But there is no way to mark f() this way.

works, you have no control over it. Never had, never will.

Yes, indeed - their is very hard to specify reasonable semantics for non-deterministic function in the where clause. Anyhow, even in this case it might bepossible to guarantee at least one call per row in the final result set - in my opinion it would be less efficient but much more intuitive.

But for select list, group by, order by quite reasonable semantics may be specified and implemented - and without a major performance penalty.

Anyhow, we already are going in circles so I do not see any point to continue this discussion any further.

I start to feel that you deliberately misinterpret my words.

select * from t where dt = to_date( '2005', 'yyyy' )

could *never* use an index on DT.
And what? How this statement is related to our discussion?

Once again, you are ascribing procedural flow of control to something that says "I AM NOT PROCEDURAL"
It is no more procedural as ROWNUM pseudocolumn behavior. Or LEVEL or - name it yourself.


It is what I call "statement-deterministic". But there is no way to mark f() this way.


Uh-uh - no way. If you run that shortly before midnight on the last day of the month and it runs for a minute or two - THE VALUE CHANGES.
That's true, I completely forgot about this peculiarity (another rather unintuitive behavior - why default month is current but default day is 1?).


You cannot pick and choose what is "statement deterministic" or not. Basically, anything that is non-deterministic EVER would have to fall into your rule.
??? It even does not matter if the function is really statement-deterministic or is completely deterministic. It rather matter of the possibility to specify semantics.

There cannot be exceptions like that just because you (and you personally) feel it would be OK.
Exceptions from what?


I cannot believe you wrote that really - the "without a major performance penalty".

Exactly - without a major performance penalty (unless the calculation of the function itself is very expensive). If it is not used in the where clause at some level of query it execution may be delayed to the last moment. So it does not affect your optimizations.

Hi Tom

Please review the following code. It seems what you've said in a follow up earlier is not true. Could you explain?
select * from (select * from t order by x) behaves same as select * from t order by x

SQL> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production    
PL/SQL Release 11.2.0.2.0 - Production                                          
CORE 11.2.0.2.0 Production                                                      
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production               
NLSRTL Version 11.2.0.2.0 - Production                                          

SQL> drop table t7 purge;
drop table t7 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> create table t7 as select * from all_objects where rownum < 11 order by created desc;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'t7');

PL/SQL procedure successfully completed.

SQL> set autot traceonly explain
SQL> select * from t7;

Execution Plan
----------------------------------------------------------                      
                                                                                
----------------------------------------------------------                      
| Id  | Operation         | Name | Rows  | Bytes | Cost  |                      
----------------------------------------------------------                      
|   0 | SELECT STATEMENT  |      |    10 |   760 |     3 |                      
|   1 |  TABLE ACCESS FULL| T7   |    10 |   760 |     3 |                      
----------------------------------------------------------                      
                                                                                
Note                                                                            
-----                                                                           
   - 'PLAN_TABLE' is old version                                                

SQL> select * from t7 order by created;

Execution Plan
----------------------------------------------------------                      
                                                                                
-----------------------------------------------------------                     
| Id  | Operation          | Name | Rows  | Bytes | Cost  |                     
-----------------------------------------------------------                     
|   0 | SELECT STATEMENT   |      |    10 |   760 |     4 |                     
|   1 |  SORT ORDER BY     |      |    10 |   760 |     4 |                     
|   2 |   TABLE ACCESS FULL| T7   |    10 |   760 |     3 |                     
-----------------------------------------------------------                     
                                                                                
Note                                                                            
-----                                                                           
   - 'PLAN_TABLE' is old version                                                

SQL> select * from (select * from t7 order by created);

Execution Plan
----------------------------------------------------------                      
                                                                                
-----------------------------------------------------------                     
| Id  | Operation          | Name | Rows  | Bytes | Cost  |                     
-----------------------------------------------------------                     
|   0 | SELECT STATEMENT   |      |    10 |   760 |     4 |                     
|   1 |  SORT ORDER BY     |      |    10 |   760 |     4 |                     
|   2 |   TABLE ACCESS FULL| T7   |    10 |   760 |     3 |                     
-----------------------------------------------------------                     
                                                                                
Note                                                                            
-----                                                                           
   - 'PLAN_TABLE' is old version                                                

SQL> drop table t7 purge;

Table dropped.

SQL> spool off

There cannot be exceptions like that just because you (and you personally) feel it would be OK.
Exceptions from what?

Sysdate is even less deterministic - but it always called once and only once. Oracle effectively declares it to be a "statement-deterministic".

either non-deterministic functions are evaluated once per row per use in the row - or they are not. You cannot go both ways.

Definitely, I can. It is not about the nature of the function as such as about declared semantic. If I declare a function to be a "statement-deterministic" (as Oracle do with sysdate, may propse another name if you do not like "statement-deterministic") then Oracle must to call it once only.


simply the OVERHEAD of invoking the function - even if the function does nothing - is HUGE and don't forget:

Only due to the current Oracle implementation, it is not an inherent property of such calls (yes, there were good reasons to implement it so).

would have to full scan - no indexes. I would call that *expensive*.
No amount of the performance improvement justifies a badly broken semantics. Never, ever!
Otherwise all programs would consist from the single Assembler instruction

ret

Very interesting discussion. I better understand what I had misunderstood, see "My Oracle Support" forum:
http://tinyurl.com/filetering-with-dbms-random
Thank you Tom for the detailed explanations, and tanks a lot for the "workaround": yes, we can make sure that the function is called once for each row ;-)
Best regards!

Hi Tom,

You said way back up at the top of the thread that we can't be sure whether a function will be called once per query, once per row, or something else. Does that mean it could be called more than once, or even zero times per row?

WITH tbl AS (SELECT 1 AS id, 'X' AS data FROM dual UNION ALL
SELECT 2, 'Y' FROM dual UNION ALL
SELECT 3, 'Z' FROM dual
)
SELECT data
FROM tbl
WHERE id = trunc(dbms_random.value(1, 4))
;

This query returns some number of rows between zero and three for me in 11gr1 and 10gr2XE.

Testing further with

DECLARE
TYPE result_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

l_results result_tbl;
l_result NUMBER;

FUNCTION f RETURN NUMBER
AS
l_count NUMBER;
BEGIN
WITH tbl AS (SELECT 1 AS id, 'A' AS data FROM dual UNION ALL
SELECT 2, 'B' FROM dual UNION ALL
SELECT 3, 'C' FROM dual UNION ALL
SELECT 4, 'D' FROM dual UNION ALL
SELECT 5, 'E' FROM dual UNION ALL
SELECT 6, 'F' FROM dual UNION ALL
SELECT 7, 'G' FROM dual UNION ALL
SELECT 8, 'H' FROM dual UNION ALL
SELECT 9, 'I' FROM dual UNION ALL
SELECT 10, 'J' FROM dual
)
SELECT count(data)
INTO l_count
FROM tbl
WHERE id = trunc(dbms_random.value(1, 11))
;
RETURN l_count;
END;
BEGIN
FOR i IN 1..100000 LOOP
l_result := f();
BEGIN
l_results(l_result) := l_results(l_result) + 1;
EXCEPTION WHEN no_data_found THEN
l_results(l_result) := 1;
END;
END LOOP;

FOR i IN l_results.FIRST..l_results.LAST LOOP
dbms_output.put_line('i: ' || i || ', count: ' || l_results(i));
END LOOP;
END;
/

gives me results like

i: 0, count: 34641
i: 1, count: 39201
i: 2, count: 19140
i: 3, count: 5726
i: 4, count: 1113
i: 5, count: 153
i: 6, count: 23
i: 7, count: 3

I get the same results regardless of whether I select from a table or an inline view.

I can understand getting multiple results (and the distribution of rows returned that is greater than 1) per id since I can't guarantee how many times dbms_random.value will be called, but surely I can expect it to be called at least once?

But if I can't then why would the query above ever return no rows?

HI,
I have a source Rate table like :

From Currency To Currency Conversion Rate
A B 2
B C 2.5
D B 3
B K 1.4
E F 3.1
R C 2.2
A L 1.9


I want to build the target rate table look like:

From Currency To Currency Conversion Rate
A B 2
B C 2.5
A C 5
D B 3
B K 1.4
A K 2.8
E F 3.1
R C 2.2
A L 1.9


Means there is no direct conversion between A to C but has indirect path like A to B and B to C.

A C 5

Also A to B and B to K so get A to K :
A K 2.8

Please suggets the way in a single sql query to solev this problem...

Thanks Tom, for some reason I didn't even consider the possibility that it would be called once per comparison.

I really appreciate all the work you put in helping people out, I'm constantly impressed by how well you mentor anonymous people. Reading Ask Tom has taught me probably most of what I know about both Oracle and SQL in general.

What does your question have to do with random numbers. Are you using random numbers for your currenecy conversions.....?

sokrates@11.2 > create function rand return number is begin dbms_output.put_line('rand called'); return dbms_random.value; end rand;
  2  /

Function created.

sokrates@11.2 > set autotr on explain
sokrates@11.2 > with x as (select dbms_random.value r from dual) select * from x where r=r and r!=r;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 885813501

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  VIEW            |      |     1 |    13 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R" IS NOT NULL AND "R"<>"R")

how often dbms_random.value was called here ?
I would guess "once".

However, different behaviour with user-defined function rand:

sokrates@11.2 > with x as (select rand r from dual) select * from x where r=r and r!=r;

         R
----------
.215245124

rand called
rand called
rand called
rand called

Execution Plan
----------------------------------------------------------
Plan hash value: 4034615273

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  FILTER          |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RAND"()<>"RAND"() AND "RAND"() IS NOT NULL)

ops$tkyte%ORA11GR2> with x as (select /*+ materialize */ rand r from dual) select * from x where r=r and r!=r;

no rows selected

rand called

Execution Plan
----------------------------------------------------------
Plan hash value: 3040811146

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6644_5FD48AF |       |       |            |          |
|   3 |    FAST DUAL               |                            |     1 |       |     2   (0)| 00:00:01 |
|*  4 |   VIEW                     |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6644_5FD48AF |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("R" IS NOT NULL AND "R"<>"R")

This topic has totally altered my perception about function in a SQL.

Wow, as I got close to catching up to date on this question I started to ponder the effect of WITH and materialize on it.

Bingo, next scroll down there is Socrates and Tom discussing exactly those things.

Hello Sir,

why optimizer put - filter("R" IS NOT NULL AND "R"<>"R")

when its obivious that r=r and r!=r from the query that it will never be true and no rows will be produce? can you please explain.


Thank you

Thanks Sir for quick response to clarify my understanding further more...

sokrates@11.2 > with x as (select rand r from dual) select * from x where r=r and r!=r;

         R
----------
.215245124

rand called
rand called
rand called
rand called

Execution Plan
----------------------------------------------------------
Plan hash value: 4034615273

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  FILTER          |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RAND"()<>"RAND"() AND "RAND"() IS NOT NULL)

is it above query get un-nested as below


select rand() from dual where rand()=rand() and rand()!=rand()


so rand() will be call first time in select, then second time during evaluting condition rand()=rand() and during last condition its call 2 times
and thats how its print the message 4 times?

Please correct me if i am wrong.


Thank you

That one bothers me.
If the function were called in place of the r references the way it winds up working seems intuitive, accessing it through the WITH clause not so much.
How is the WITH clause defined?

is it above query get un-nested as below


select rand() from dual where rand()=rand() and rand()!=rand()


so rand() will be call first time in select, then second time during evaluting condition rand()=rand() and during last condition its call 2 times
and thats how its print the message 4 times?

Actually the order of calls seem to differ slightly, the result of the select seems to be the last call, not the first call, at first the conditions are evaluated:

sokrates@11.2 > create or replace
  2  function rand return number
  3  is
  4  r number := dbms_random.value;
  5  begin
  6  dbms_output.put_line('rand called - will return ' || r);
  7  return r;
  8  end rand;
  9  /

Function created.

sokrates@11.2 > with x as (select rand r from dual) select * from x where r=r and r!=r;

         R
----------
.556546596

rand called - will return .3848935116626815464233585003749046243
rand called - will return .97113274370680783878482004983917496957
rand called - will return .36883776673117203532610477335486686666
rand called - will return .55654659631275442000486278461821038313

Execution Plan
----------------------------------------------------------
Plan hash value: 4034615273

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  FILTER          |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RAND"()<>"RAND"() AND "RAND"() IS NOT NULL)

The select delivers the result of the last call.

why optimizer put - filter("R" IS NOT NULL AND "R"<>"R")

when its obivious that r=r and r!=r from the query that it will never be true and no rows will be
produce?

That's a very interesting question in my optinion.

Assume, that the optimizer would see the "obvious contradiction" and from that would deliver "no rows selected". In other words, you would expect that the optimizer would "see" any "obvious" contradiction in a SQL, wouldn't you ?

But this wouldn't be a "micro-optimization", quite the opposite: the implementation of such an optimization would have to solve the boolean satisfiability problem, which is known to be NP-hard, see
http://en.wikipedia.org/wiki/Boolean_satisfiability_problem

Well, I can understand why rand() could be called 4 times in the case of Socrates query written without the WITH clause for the reasons you've given.
But for the life of me I can't understand why the optimizer would simply rewrite the WITH clause out of the query as given just above.
At least that's the only explanation I can think of for the 4 calls to rand()
I thought the WITH clause was kind of like a directive to "do this first and reuse the results"

Interesting.
So WITH can be thought of as an alternate inline view syntax?

Our database even rewrites it when a materialize hint is used:

chajol_dba@taxtest>ed
Wrote file afiedt.buf

1 with x
2 as (/*+ materialize */
3 select rand r from dual)
4 select *
5 from x
6* where r=r and r!=r
chajol_dba@taxtest>/

R
----------
.140225199

1 row selected.

rand called - will return .76853973916567692167596730551150623272
rand called - will return .5396196175405806460117863850708532041
rand called - will return .37414826008658638489073832782549908657
rand called - will return .14022519860129962410244274383917663374
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 4034615273

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RAND"()<>"RAND"() AND "RAND"() IS NOT NULL)

chajol_dba@taxtest>

I'm lucky in that I sit a mere tens of feet from my users and can fix things, if a plan goes bad, in a matter of minutes.

On the other hand:

chajol_dba@taxtest>ed
Wrote file afiedt.buf

1 with x
2 as (select rand() r from dual connect by level < 2)
3 select *
4 from x
5* where r=r and r!=r
chajol_dba@taxtest>/

no rows selected

rand called - will return .48506043344076145464260047750362969475
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2403765415

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("R" IS NOT NULL AND "R"<>"R")
2 - filter(LEVEL<2)

chajol_dba@taxtest>

OK, thanks

Yeah, it was a typo on my part. The materialize hint does change the plan. I put the corrected code here just for completeness.
These edge cases are interesting because they are like the C code you can find out there that is designed to trick compilers into making mistakes.

chajol_dba@taxtest>ed
Wrote file afiedt.buf

1 with x
2 as
3 (select /*+ materialize */
4 rand r
5 from dual)
6 select *
7 from x
8* where r=r and r!=r
chajol_dba@taxtest>/

no rows selected

rand called - will return .97600706740531863178218579740983563455
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 791862530

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D683A_5017A14F | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 4 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D683A_5017A14F | 1 | 13 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("R" IS NOT NULL AND "R"<>"R")

chajol_dba@taxtest>

I have a requirement wherein a value in a given column needs to be distributed amongst two or three columns equally or otherwise.

For eg:

CREATE TABLE TT(c1 number,c2 number, c3 number,c4 number)

insert into tt values(.33,.33,.34,3)
insert into tt values(.33,.33,.34,5)
insert into tt values(.5,.5,null,5)

the column c4 needs to be distributed amongst c1,c2,c3 provided the values in c1,c2,c3 are not 0 or 1.

So in the above case, I would like to have the output as:

1,1,1,3
1,2,2,5 this could also be 2,1,2,5 or any such distrubtion
2,3,null,5

Trying to use DBMS_RANDOM but looks like I am not able to achieve
Thanks a lot
Thiru