Oracle 18.1
I have a pipelined PL/SQL function (pipe_fn) which takes a single numeric parameter and returns a collection type with multiple columns, as described here.
Given the same input parameter, the output is the same i.e. the function is deterministic.
I read Tim's excellent article on this but it doesn't quite answer my question.
The function is used in a SQL query of the form
select
...
from large_table lt
join table(pipe_fn(lt.id)) pf on pf.id = lt.id
I verified that the function executes for each row in large_table (using a sequence nextval in the function code)
How can I reduce the function executions to only the first occurence of LT.ID and use some sort of cache for the others?
Or am I thinking about this wrong? Any ideas appreciated.
Thanks