RE: Semi-deterministic?

> I think his question is… how can Oracle cache subquery results safely if
the function is NOT deterministic? That is, how does it know the results
> wouldn`t change from row to row?

I guess I understood question correctly :) i just meant that number of executions of the whole scalar subquery does not depend on the presence of any function within it.
As i wrote before it depends only on cache size("_query_execution_cache_max_size" parameter), number of different outer input parameters, their hash-collisions and order. You can read about it in details:
* Tom Kyte - "On Caching and Evangelizing SQL": http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html * Jonathan Lewis - "Cost-Based Oracle fundamentals" chapter 9

I also compared caching mechanisms of scalar subquery caching and deterministic functions in 11.2:
http://orasql.org/category/oracle/scalar-subquery-caching/

Btw, there are some significant changes in 12c: * Scalar subquery from select-list can be unnested now: http://blog.tanelpoder.com/2013/08/13/oracle-12c-scalar-subquery-unnesting-transformation/ * Correlated subquery can reference to a column from parent tables more than one level above

--
Best regards,
Sayan Malakshinov
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l