Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Generate /*+FIRST_ROWS*/ hint with Oracle 12c FETCH clause #5793

Open
lukaseder opened this Issue Jan 18, 2017 · 2 comments

Comments

Projects
None yet
1 participant
@lukaseder
Copy link
Member

lukaseder commented Jan 18, 2017

Apparently, Oracle 12c gets cardinality estimates wrong with the FETCH clause (unlike with ROWNUM filtering). This is bad news as jOOQ 3.9 by default generates the FETCH clause for limit(). Of course, we must not put an exact cardinality there from the LIMIT clause (if LIMIT uses bind values), in order to prevent too many hard parses. There are multiple strategies that might apply: 1, 10, 100, 1000, 10000... Or 1, 2, 4, 8, 16, 32, ...

As a workaround, users can add the hint manually, using hint("/*+FIRST_ROWS(n)*/").

This should be governed by a new Settings.oracleHintFirstRowsOnLimit:

  • OFF (default): Don't add any hints
  • EXACT: Put the exact number of expected rows in the hint
  • LOG2: Put the next power of 2 in the hint (3 -> 4, 5 -> 8, 11 -> 16)
  • LOG10: Put the next power of 10 in the hint (3 -> 10, 5 -> 10, 11 -> 100)

In all cases, the number of rows to be calculated is (limit - offset), and it is done only if the values are available - i.e. if they are literals or bind variables, not expressions.


See also:

@lukaseder

This comment has been minimized.

Copy link
Member Author

lukaseder commented Feb 5, 2017

Execution plans (and speeds!) are indeed much worse without /*+FIRST_ROWS(n)*/ hints:

image

@lukaseder

This comment has been minimized.

Copy link
Member Author

lukaseder commented Jun 25, 2018

Here's a blog post about the topic:
https://blog.jooq.org/2018/06/25/oracles-offset-fetch-is-slower-than-classic-rownum-filtering/

The execution time differences can be devastating. We definitely have to add this hint to jOOQ generated queries

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.