Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: open-jpa-dev@incubator.apache.org
Message-ID: <25683714.1173726069678.JavaMail.jira@brutus>
Date: Mon, 12 Mar 2007 12:01:09 -0700 (PDT)
From: "David Wisneski (JIRA)" <jira@apache.org>
To: open-jpa-dev@incubator.apache.org
Subject: [jira] Commented: (OPENJPA-168) sql optimize n rows query hint
In-Reply-To: <31084175.1173724809966.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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