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

Oracle hints

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

Oracle implements hints using a comment style syntax, where the multi line comment contains a special + token to distinguish it from an ordinary comment, e.g. /*+HINT*/.

For example, the following hint tells the optimiser that the client is going to consume all the rows from the result set, as opposed to aborting the fetch after a few rows:

SELECT /*+ALL_ROWS*/ FIRST_NAME, LAST_NAME
  FROM AUTHOR

This can be done in jOOQ using the .hint() clause in your SELECT statement:

create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .hint("/*+ALL_ROWS*/")
      .from(AUTHOR)
      .fetch();

Note that you can pass any string in the .hint() clause, including any non-hint comment if you wish to use this syntax to mark your queries. If you use that clause, the passed string will always be put in between the SELECT [DISTINCT] keywords and the actual projection list. This can be useful in other databases too, such as MySQL, for instance:

SELECT SQL_CALC_FOUND_ROWS field1, field2
FROM table1

 
create.select(field1, field2)
      .hint("SQL_CALC_FOUND_ROWS")
      .from(table1)
      .fetch()

See also Oracle-style hints in MySQL for more details.

Feedback

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

The jOOQ Logo