RE: Semi-deterministic?
- From: Sayan Sergeevich Malakshinov <malakshinovss@xxxxxxxxx>
- To: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>
- Date: Thu, 6 Feb 2014 11:16:21 +0400
Hi Mark,
> 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
Other related posts:
