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

Breadcrumb

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
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 ?

with LiveSQL Test Case:

and Chris said...

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

Rating

  (3 ratings)

Comments

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

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:
https://livesql.oracle.com/apex/livesql/s/on0b6hmjohkax3kb8jn70sasz
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

SQL

The Oracle documentation contains a complete SQL reference.