Available in versions: Dev (3.20) | Latest (3.19) | 3.18

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!

The jOOQ Logo