Of course, Martin Berger is correct once more.
- The function is obsolete and should be removed
- The function is not defined as deterministic, though it is maximal deterministic. As a consequence, one can not create a function based index, create no virtual column and no Extended Statistics.
- of course, it would make sense to use the result cache. In the function definition for this keyword is missing. We can not add it without changing the code.
- As a final comprehensive measure we could assign statistics to the function. The how to is described here Table Expressions, Cardinality, SYS_OP_ATG and KOKBF and here
setting cardinality for pipelined table functions and code. Unfortunately this is not possible because an error in the where condition enforces an implicit conversion. Statistics assigned to function effective are not used if to_number (effective ..) which is actually called. Statistics associated with the original effective function are therefore ineffective.
Thus, the developer has destroyed all possibilities for an elegant solution due impressive incompetence and we need to fix the statement using hints. This was achieved through a sql patch