Persistence.xml and the Oracle hint FIRST_ROWS

I found out that it is important to specify the exact version of Oracle in the persistence.xml file. I am currently using EclipseLink as the persistence framework provider.

Oracle hints were introduced to speed up/optimize SQL queries. Starting with Oracle 10, some of them have become almost unecessary because the optimizer was greatly improved. Also, depending on the structure of the queries and the database design (use of primary keys or not, index are where they should be or not, etc), hints can be counterproductive since they slow down queries instead of speeding them up.

SELECT * FROM (
SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (
SELECT col1, col2 from table1)
a WHERE ROWNUM <= 20)
WHERE rnum > 0;

This SQL query is used for pagination, was coded with JPA criteria and was generated by EclipseLink using ORACLE as the target database as specified in the following persistence.xml file :

<property name="eclipselink.target-database" value="Oracle" />

We use Oracle 11g. In order to indicate EclipseLink to produce SQL code for Oracle11, the target database should be ORACLE11, not ORACLE:

<property name="eclipselink.target-database" value="Oracle11" />

In that case, the SQL query generated no longer contains the FIRST_ROWS hint :

SELECT * FROM (
SELECT a.*, ROWNUM rnum  FROM (
SELECT col1, col2 from table1)
a WHERE ROWNUM <= 20)
WHERE rnum > 0;

If we examine the source code of EclipseLink, we can see in OraclePlatform.java the initial value of the HINT variable :

<b>protected</b> String HINT = "/*+ FIRST_ROWS */ ";

In the Oracle10Platform class, which inherits from OraclePlatform, that value is overriden :

public Oracle10Platform(){
super();
//bug 374136: override setting the FIRST_ROWS hint as this is not needed on Oracle10g
HINT = "";
}

EclipseLink error : Null primary key encountered in unit of work clone

With EclipseLink used as the JPA provider, the following error message “Null primary key encountered in unit of work clone” may appear if the value 0 is used for a field which is a primary key.

To resolve it, a few solutions :

1) start your primary key values from 1 instead of 0, which in my opinion is the best solution.

2) add the following annotation to the entity :


import javax.persistence.Entity;
import javax.persistence.Table;

import org.eclipse.persistence.annotations.IdValidation;
import org.eclipse.persistence.annotations.PrimaryKey;


/**
 * The persistent class for the HOUSE database table.
 * 
 */
@Entity
@Table(name = "HOUSE")
@PrimaryKey(validation=IdValidation.NULL)
public class House implements Serializable {

...

}

3) add the following property to the persistence.xml file :


<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
	xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">

	<persistence-unit name="myUnit">

		<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
		<jta-data-source>jdbc/MySource</jta-data-source>
		
		<class>com.blabla.myEntity</class>
						
		<properties>
                    <property name="eclipselink.allow-zero-id" value="true"/> 
		</properties>

	</persistence-unit>

</persistence>

Links :
http://eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_primarykey.htm