Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

Scalar subqueries for stored functions

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

This setting is useful mostly for the Oracle database, which implements a feature called scalar subquery caching, which is a good tool to avoid the expensive PL/SQL-to-SQL context switch when predicates make use of stored function calls.

With this setting in place, all stored function calls embedded in SQL statements will be wrapped in a scalar subquery:

SELECT
  (SELECT my_package.format(LANGUAGE_ID) FROM dual)
FROM BOOK
DSL.using(configuration)
   .select(MyPackage.format(BOOK.LANGUAGE_ID))
   .from(BOOK)

If our table contains thousands of books, but only a dozen of LANGUAGE_ID values, then with scalar subquery caching, we can avoid most of the function calls and cache the result per LANGUAGE_ID.

Example configuration

Settings settings = new Settings()
    .withRenderScalarSubqueriesForStoredFunctions(true);

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo