I have a function called generate_random_number which returns random number if it’s called without parameters. If parameter value is specified it returns the same value as the input parameter. The random number that is returned is build with dbms_random and it can be any number between 0 and 10.
If I run the following statement where I’m trying to build random value by calling generate_random_number and using it as projection in the upper query block, I would get inconsistent results. Notice, how I am using the value from the inner block in projection, number1 and number1 as number2.
Same “value” different results, interesting. Let see the execution plan, +outlines so we can see what the optimizer is trying to accomplish.
Notice the MERGE(@”SEL$2″), oracle is clever enough to detect that inner result is used in the upper query block and it performs simple view merging. But, what it doesn’t know is that the function generate_random_number uses dbms_random package and therefore might return different value for each call.
Here is the final plan used by the optimizer after query transformation. This information is produced with dbms_sqldiag.dump_trace.
This explains from where the different (inconsistent) values are coming. The generate_random_number is called twice therefore we get two different values.
There are different workarounds for this behavior. You can put DISTINCT in the inner block to restrict the optimizer from doing view merging transformation. It will do the job for this particular example since I am generating only one row.
If you are not allowed to modify the SQL statement, set “_simple_view_merging” = false at session level.
You can also hint the inner query block statement with /*+ NO_MERGE */.