If this queries the database I am not sure it can be deterministic.
Instead I suggest a wrapper function that uses the result cache.
Best would be a function in the with clause to minimise context switch.
Regards
Lothar
Am 04.02.2021 um 08:58 schrieb Sayan Malakshinov:
Hi Mohamed,
+my posts describing how does it work:
Deterministic Functions and Scalar Subquery Caching:
http://orasql.org/2013/02/10/deterministic-function-vs-scalar-subquery-caching-part-1/ <http://orasql.org/2013/02/10/deterministic-function-vs-scalar-subquery-caching-part-1/>
http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-2/ <http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-2/>
http://orasql.org/2013/03/13/deterministic-function-vs-scalar-subquery-caching-part-3/ <http://orasql.org/2013/03/13/deterministic-function-vs-scalar-subquery-caching-part-3/>
http://orasql.org/2014/03/31/deterministic-functions-result_cache-and-operators/ <http://orasql.org/2014/03/31/deterministic-functions-result_cache-and-operators/>
http://orasql.org/2013/03/13/optimization-of-loops-in-plsql-part-1/ <http://orasql.org/2013/03/13/optimization-of-loops-in-plsql-part-1/>
http://orasql.org/2013/06/10/too-many-function-executions/ <http://orasql.org/2013/06/10/too-many-function-executions/>
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org ;<http://orasql.org>
чт, 4 февр. 2021 г., 10:14 Mohamed Houri <mohamed.houri@xxxxxxxxx <mailto:mohamed.houri@xxxxxxxxx>>:
/or you can just wrap it into scalar subquery ... = (select
fnd_profile.value('ORG_ID') from dual)/
/for scalar subquery caching/
/
/
That's exactly what I have explained in the above-mentioned blog post
https://hourim.wordpress.com/2019/12/18/scalar-subquery-caching-the-select-from-dual-trick/
<https://hourim.wordpress.com/2019/12/18/scalar-subquery-caching-the-select-from-dual-trick/>
Best regards
Mohamed
Le jeu. 4 févr. 2021 à 00:27, Sayan Malakshinov
<xt.and.r@xxxxxxxxx <mailto:xt.and.r@xxxxxxxxx>> a écrit :
or you can just wrap it into scalar subquery ... = (select
fnd_profile.value('ORG_ID') from dual)
for scalar subquery caching
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect
-<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>_Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>_
My Twitter <https://twitter.com/MohamedHouri> -MohamedHouri
<https://twitter.com/MohamedHouri>