openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Wisneski (JIRA)" <>
Subject [jira] Commented: (OPENJPA-168) sql optimize n rows query hint
Date Mon, 12 Mar 2007 19:01:09 GMT


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
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 


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:
>             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.

View raw message