|The jOOQ User Manual : SQL building : The DSLContext API : Custom Settings : Scalar subqueries for stored functions||previous : next|
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
Settings settings = new Settings() .withRenderScalarSubqueriesForStoredFunctions(true);