[
https://issues.apache.org/jira/browse/OPENJPA-168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12480171
]
David Wisneski commented on OPENJPA-168:
----------------------------------------
By way of background, several databases support a sql optimizer hint with
respect to optimiziing the sql query for all rows or optimizing for quick
retrieval of a few rows. This is different from the FETCH LIMIT in sql which
puts a hard limit on the number of rows returns. The optimize hint only
affects optimiziation. Not all database support this syntax and the syntax is
different for each database.
Derby does not support.
DB2 supports with syntax OPTIMIZE FOR n ROW(S) which appears toward the end of
the sql select statement.
Oracle supports with syntax /*+ FIRST ROWS(N) */ or /*+ ALL ROWS */ which
occurs after the select keyword.
SQLServer supports but the syntax is different from DB2, Oracle.
Since the concept applies to multiple (but not all) databases and the syntax is
vendor specific, we propose a hint with the name
openjpa.hints.SQLOptimizeRows
which can be assigned a positive integer value.
This value will be verified on the setHint call and if it is not a
java.lang.Integer and a positive value, an ArgumentException will be thrown.
For native queries, the hint, if specified, will be ignored.
The hint value will be stored on the FetchConfiguration instance.
For internally generated selects, such as from EntityManager.find or
relationship navigation, we also want to indicate if one row is the expected
size of the result set. Also in the case of Query.getSingleResult we want to
indicate an expected size of one.
To do this we will have a instance variable n FetchConfiguration to track the
fetch operation: getResultList, getSingleResult, find, etc.
So FetchConfiguration will contain the user supplied hint value OR a indication
of the operation that generated the select.
We modify SelectImpl to add instance variables for expectedResultCount and
boolean hasSingleValuesJoins. When multi valued relationships are added to a
select through the EagerMap the expectedResultCount will be appropriately
modified to indicate it is not a single value select. even if the original
internal operation (i.e. find ) would normally be a single row resut.
Finally the Dictionary class will have new method getOptimizeClause. Database
Dictionaries that support some form of OPTIMIZE FOR will have to override the
two toSelect methods so that the expectedResultCout from selectImpl is emitted
into the sql.
> sql optimize n rows query hint
> ------------------------------
>
> Key: OPENJPA-168
> URL: https://issues.apache.org/jira/browse/OPENJPA-168
> Project: OpenJPA
> Issue Type: New Feature
> Reporter: David Wisneski
> Assigned To: David Wisneski
>
> There werre various comments from Patrick, Abe and Kevin Sutter about the
> code that I checked related to Optimize hint. So I have gone back and
> relooked at this and wil be making some changes. At Kevin's suggestion I
> will do this through a JIRA feature so that folks will have opportunity to
> comment on this before the code is actually done and checked in.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
