For code and test output, please see LiveSQL Link
https://livesql.oracle.com/apex/livesql/s/onh4jsczpzaa2bp9t0r9yfkxh When called with:
exec test_proc(1000, 1, 5)
the output shows 500 vs. 77. Why InDirect Count = 77 calls ?
Statement processed.
---------- Compare test_proc(1000, 1, 5) Function-Calls -------------
Direct Count = 500
InDirect Count = 77
In following calls, we can see that test_proc(183, 0, 5) vs test_proc(184, 0, 5), InDirect Count changed from 50 to 51.
But we can see that test_proc(186, 1, 5) vs test_proc(187, 1, 5), InDirect Count changed from 50 to 51.
Why such call number difference ?
You're seeing the effects of scalar subquery caching
With:
select * from test_tab where x = p_x and p_y = test_cond(y)
The database will execute test_cond for every row it processes.
When you place the function in a subquery:
select * from test_tab
where x = p_x
and (p_y = (select test_cond(y) from dual))
The database can optimize the subquery to only execute once for each value of Y. See more at:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2683853500346598211