Re: Query runtime is slow in view

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_at_gmail.com
> <mailto:mohamed.houri_at_gmail.com>>:
>
>
> /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_at_gmail.com <mailto:xt.and.r_at_gmail.com>> 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>
>

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