Skip to Main Content
  • Questions
  • Different number of where condition function call between direct and indirect calls


Question and Answer

Chris Saxon

Thanks for the question, Kun.

Asked: January 20, 2023 - 8:40 am UTC

Last updated: January 23, 2023 - 5:16 pm UTC

Version: 19.13, 19c

Viewed 100+ times

You Asked

For code and test output, please see LiveSQL Link

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 ?

with LiveSQL Test Case:

and Chris said...

You're seeing the effects of scalar subquery caching


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:


  (3 ratings)


Scalar subquery Caching

kun sun, January 21, 2023 - 5:50 am UTC

Many thanks for the quick and exact answer.
Connor McDonald
January 23, 2023 - 6:50 am UTC

glad to help


Kun, January 23, 2023 - 7:49 am UTC

I made one more test with a Non-deterministic Plsql Function.
It returns different result With vs. Without scalar subquery caching.

See: LiveSQL:
Chris Saxon
January 23, 2023 - 11:47 am UTC

What exactly is your question?

Subquery caching is not guaranteed to cache every function call; there's a limit to how many values are cached; the optimizer may transform the subquery away, etc.

So are you just trying to understand what's happening, or is this causing unexpected results in the application?

If it's the latter (unexpected results) the bottom line is you should avoid calling functions that have side effects (like setting package state) in SQL.

Scalar subquery Caching

Kun, January 23, 2023 - 2:52 pm UTC

No, it is not a question.
I just want to make some more LiveSQL tests to share with Oracle community,
and check my understanding.
Chris Saxon
January 23, 2023 - 5:16 pm UTC

Thanks for sharing

More to Explore


The Oracle documentation contains a complete SQL reference.