Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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!