Re: Should we use Subquery caching or result cache here

Hi Gacek,

You can compare also my scalar sql macro variant sent 3 days ago: https://www.freelists.org/post/oracle-l/Should-we-use-Subquery-caching-or-result-cache-here,3

 It was a bit shorter.
And don't forget that scalar sql macros are available only for oracle 21.

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE Associate
http://orasql.org

вт, 2 нояб. 2021 г., 1:34 Jacek Gębal <jgebal_at_gmail.com>:

> I did a simple test comparing time needed to run a SQL with a scalar SQL
> Macro function vs running it with regular function (deterministic & UDF).
> The deterministic can be ignored as there are no duplicate input
> parameters in this test.
>
> create or replace FUNCTION fun2(INAMT IN NUMBER, INSTS IN VARCHAR2
> , INPDT IN DATE,IN_STDT IN DATE, IN_ENDT IN DATE, INBK IN
> VARCHAR2)
> RETURN number deterministic IS
> pragma udf;
> BEGIN
> return CASE
> WHEN INBK = 'Y' THEN
> CASE
> WHEN (INSTS = 'AA' AND INPDT > IN_ENDT) THEN INAMT
> WHEN (INSTS IN ('SS','BB', 'TT','ZZ')) THEN INAMT
> ELSE 0
> END
> WHEN (INBK = 'X') AND INSTS = 'AA' THEN
> CASE
> WHEN to_char(INPDT, 'HH:MI:SS AM') = '12:00:00 AM'
> AND TO_DATE(TO_CHAR(INPDT,'MM/DD/YYYY') || ' ' || TO_CHAR
> (IN_STDT, 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM') >= IN_STDT
> AND TO_DATE(TO_CHAR(INPDT,'MM/DD/YYYY') || ' ' || TO_CHAR
> (IN_STDT, 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM') < IN_ENDT
> THEN INAMT
> WHEN to_char(INPDT, 'HH:MI:SS AM') <> '12:00:00 AM'
> AND INPDT >= IN_STDT AND INPDT < IN_ENDT
> THEN INAMT
> ELSE 0
> END
> ELSE 0
> END;
> END;
> /
>
>
>>>>>>>>> ===========================================================================================================================================
>>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>

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