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!