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@xxxxxxxxx>:
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;
/
===========================================================================================================================================
