하이버네이트 사용시에 오라클에서 날짜 형식으로 조회시에 Index 안

하이버네이트 사용시에 오라클에서 날짜 형식으로 조회시에 Index 안타는 문제가 발생..

내용을 요약하면.. 하이버네이트가 날짜 형식으로 인식 못하고 파라미터 기준으로
문자 ( 필드 ) 와 날짜 ( 파라미터)라 판단 하고 필드를 함수 쒸어서
날짜 형식으로 변경 함.. 그래서 인덱스 못 탐..

결론 적으로 하이버 네이트한데 이 필드가 날짜 형식이라는걸 인식 해줘야 함.

What is the problem with Oracle DATE?

The problem that was presented in the previous article is dealing with the fact that if a query uses filters on Oracle DATE columns:

// execute_at is of type DATE and there's an index
PreparedStatement stmt = connection.prepareStatement(
"SELECT * " +
"FROM rentals " +
"WHERE rental_date > ? AND rental_date < ?");

… and we’re using java.sql.Timestamp for our bind values:

1.stmt.setTimestamp(1, start);

2.stmt.setTimestamp(2, end);

… then the execution plan will turn very bad with a FULL TABLE SCAN or perhaps an INDEX FULL SCAN, even if we should have gotten a regular INDEX RANGE SCAN.

-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|*  1 |  FILTER            |        |
|*  2 |   TABLE ACCESS FULL| RENTAL |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:1<=:2)
   2 - filter((INTERNAL_FUNCTION("RENTAL_DATE")>=:1 AND
              INTERNAL_FUNCTION("RENTAL_DATE")<=:2))

This is because the database column is widened from Oracle DATE to OracleTIMESTAMP via this INTERNAL_FUNCTION(), rather than truncating thejava.sql.Timestamp value to Oracle DATE.

More details about the problem itself can be seen in the previous article

Preventing this INTERNAL_FUNCTION() with Hibernate

You can fix this with Hibernate’s proprietary API, using aorg.hibernate.usertype.UserType.

Assuming that we have the following entity:

@Entity
public class Rental {
 
@Id
@Column(name = "rental_id")
public Long rentalId;
 
@Column(name = "rental_date")
public Timestamp rentalDate;
}

And now, let’s run this query here (I’m using Hibernate API, not JPA, for the example):

List<Rental> rentals =
session.createQuery("from Rental r where r.rentalDate between :from and :to")
.setParameter("from", Timestamp.valueOf("2000-01-01 00:00:00.0"))
.setParameter("to", Timestamp.valueOf("2000-10-01 00:00:00.0"))
.list();

The execution plan that we’re now getting is again inefficient:

-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|*  1 |  FILTER            |        |
|*  2 |   TABLE ACCESS FULL| RENTAL |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:1<=:2)
   2 - filter((INTERNAL_FUNCTION("RENTAL0_"."RENTAL_DATE")>=:1 AND
              INTERNAL_FUNCTION("RENTAL0_"."RENTAL_DATE")<=:2))

The solution is to add this @Type annotation to all relevant columns…

@Entity
@TypeDefs(
value = @TypeDef(
name = "oracle_date",
typeClass = OracleDate.class
)
)
public class Rental {
 
@Id
@Column(name = "rental_id")
public Long rentalId;
 
@Column(name = "rental_date")
@Type(type = "oracle_date")
public Timestamp rentalDate;
}

and register the following, simplified UserType:

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Objects;
 
import oracle.sql.DATE;
 
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;
 
public class OracleDate implements UserType {
 
@Override
public int[] sqlTypes() {
return new int[] { Types.TIMESTAMP };
}
 
@Override
public Class<?> returnedClass() {
return Timestamp.class;
}
 
@Override
public Object nullSafeGet(
ResultSet rs,
String[] names,
SessionImplementor session,
Object owner
)
throws SQLException {
return rs.getTimestamp(names[0]);
}
 
@Override
public void nullSafeSet(
PreparedStatement st,
Object value,
int index,
SessionImplementor session
)
throws SQLException {
// The magic is here: oracle.sql.DATE!
st.setObject(index, new DATE(value));
}
 
// The other method implementations are omitted
}

This will work because using the vendor-specific oracle.sql.DATE type will have the same effect on your execution plan as explicitly casting the bind variable in your SQL statement, as shown in the previous article: CAST(? AS DATE). The execution plan is now the desired one:

------------------------------------------------------
| Id  | Operation                    | Name          |
------------------------------------------------------
|   0 | SELECT STATEMENT             |               |
|*  1 |  FILTER                      |               |
|   2 |   TABLE ACCESS BY INDEX ROWID| RENTAL        |
|*  3 |    INDEX RANGE SCAN          | IDX_RENTAL_UQ |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:1<=:2)
   3 - access("RENTAL0_"."RENTAL_DATE">=:1
          AND "RENTAL0_"."RENTAL_DATE"<=:2)

If you want to reproduce this issue, just query any Oracle DATE column with ajava.sql.Timestamp bind value through JPA / Hibernate, and get the execution plan as indicated here.

Don’t forget to flush shared pools and buffer caches to enforce the calculation of new plans between executions, because the generated SQL is the same each time.

Can I do it with JPA 2.1?

At first sight, it looks like the new converter feature in JPA 2.1 (which works just like jOOQ’s converter feature) should be able to do the trick. We should be able to write:

import java.sql.Timestamp;
 
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
 
import oracle.sql.DATE;
 
@Converter
public class OracleDateConverter
implements AttributeConverter<Timestamp, DATE>{
 
@Override
public DATE convertToDatabaseColumn(Timestamp attribute) {
return attribute == null ? null : new DATE(attribute);
}
 
@Override
public Timestamp convertToEntityAttribute(DATE dbData) {
return dbData == null ? null : dbData.timestampValue();
}
}

This converter can then be used with our entity:

import java.sql.Timestamp;
 
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.Id;
 
@Entity
public class Rental {
 
@Id
@Column(name = "rental_id")
public Long rentalId;
 
@Column(name = "rental_date")
@Convert(converter = OracleDateConverter.class)
public Timestamp rentalDate;
}

But unfortunately, this doesn’t work out of the box as Hibernate 4.3.7 will think that you’re about to bind a variable of type VARBINARY:

// From org.hibernate.type.descriptor.sql.SqlTypeDescriptorRegistry
 
public <X> ValueBinder<X> getBinder(JavaTypeDescriptor<X> javaTypeDescriptor) {
if ( Serializable.class.isAssignableFrom( javaTypeDescriptor.getJavaTypeClass() ) ) {
return VarbinaryTypeDescriptor.INSTANCE.getBinder( javaTypeDescriptor );
}
 
return new BasicBinder<X>( javaTypeDescriptor, this ) {
@Override
protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options)
throws SQLException {
st.setObject( index, value, jdbcTypeCode );
}
};
}

Of course, we can probably somehow tweak thisSqlTypeDescriptorRegistry to create our own “binder”, but then we’re back to Hibernate-specific API. The fact is that the JPA 2.1 converter API is not going to be sufficient for these use-cases, as it doesn’t allow you to specify how to interact with JDBC, which is often required.

Conclusion

Abstractions are leaky on all levels, even if they are deemed a “standard” by the JCP. Standards are often a means of justifying an industry de-facto standard in hindsight (with some politics involved, of course). Let’s not forget that Hibernate didn’t start as a standard and massively revolutionised the way the standard-ish J2EE folks tended to think about persistence, 14 years ago.

In this case we have:

Oracle SQL, the actual implementation
The SQL standard, which specifies DATE quite differently from Oracle
ojdbc, which extends JDBC to allow for accessing Oracle features
JDBC, which follows the SQL standard with respect to temporal types
Hibernate, which offers proprietary API in order to access Oracle SQL and ojdbc features when binding variables
JPA, which again follows the SQL standard and JDBC with respect to temporal types
Your entity model

As you can see, the actual implementation (Oracle SQL) leaked up right into your own entity model, either via Hibernate’s UserType, or via JPA’sConverter. From then on, it will hopefully be shielded off from your application (until it won’t), allowing you to forget about this nasty little Oracle SQL detail.

Any way you turn it, if you want to solve real customer problems (i.e. the significant performance issue at hand), then you will need to resort to vendor-specific API from Oracle SQL, ojdbc, and Hibernate – instead of pretending that the SQL, JDBC, and JPA standards are the bottom line.

But that’s probably alright. For most projects, the resulting implementation-lockin is totally acceptable.

Published at DZone with permission of Lukas Eder, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Andrew's Garage