SEEK clause implementation
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SEEK clause is a powerful alternative to the OFFSET clause for pagination. By default, the SEEK clause is transformed into an equivalent ROW predicate as follows:
SELECT id, value FROM t WHERE (value, id) > (2, 533) ORDER BY value, id LIMIT 5
create.select(T.ID, T.VALUE)
.from(T)
.orderBy(T.VALUE, T.ID)
.seek(lastValue, lastId)
.limit(5)
.fetch();
That ROW predicate is optimal, syntactically, but may not be optimised optimally by a dialect's underlying optimiser. As such, there are two ways to influence the generation of this predicate in away to possibly help the optimiser choose the right index:
-- Settings.renderRowConditionForSeekClause = false to turn off using the ROW syntax WHERE value > 2 OR value = 2 AND id > 533 -- Settings.renderRedundantConditionForSeekClause = true to add an additional redundant predicate WHERE value >= 2 AND (value, id) > (2, 533) WHERE value >= 2 AND (value > 2 OR value = 2 AND id > 533)
The default in jOOQ is to not do the above, but users can opt into the manual expansion of syntax to benefit performance.
Note that if the ROW syntax isn't supported natively, then jOOQ will expand that to the equivalent OR predicate anyway.
Example configuration
Settings settings = new Settings()
.withRenderRowConditionForSeekClause(false) // Default to true
.withRenderRedundantConditionForSeekClause(true); // Default to false
Feedback
Do you have any feedback about this page? We'd love to hear it!