Oracle Query slow due to incompatible data types Date and Timestamp

The performance of sql produced by hibernate will be affected  due to incompatible data types. It appears that column data type(DATE) and hibernate data type(TIMESTAMP) mismatch causes implicit data type conversion. Oracle uses INTERNAL_FUNCTION to transfer date column to match the passed bind variable hibernate data type TimeStamp.

Resolve this Issue:

• Use  ojdbc6 version 11 (http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01)

  com.oracle
  ojdbc6
  11.2.0.4

• CustomDateTimeUserType class defined to set the date time as java.sql.Date object to map with DB Data column.               

preparedStatement.setObject(i, timestamp, java.sql.Types.DATE)



User defined hibernate date type

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.Date;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class CustomDateTimeUserType implements UserType {

 private final Logger logger = LoggerFactory.getLogger(this.getClass());

 @Override
 public int[] sqlTypes() {
  return new int[] { Types.TIMESTAMP };
 }

 @SuppressWarnings("rawtypes")
 @Override
 public Class returnedClass() {
  return Date.class;
 }

 @Override
 public boolean equals(Object x, Object y) throws HibernateException {
  return x == y || !(x == null || y == null) && x.equals(y);
 }

 @Override
 public int hashCode(Object x) throws HibernateException {
  assert (x != null);
  return x.hashCode();
 }

 @Override
 public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
   throws HibernateException, SQLException {
  Timestamp timestamp = rs.getTimestamp(names[0]);
  if (rs.wasNull()) {
   return null;
  }
  return new Date(timestamp.getTime());
 }

 @Override
 public void nullSafeSet(PreparedStatement st, Object value, int index)
   throws HibernateException, SQLException {
  // if (value == null) {
  // st.setNull(index, Types.TIMESTAMP);
  //
  // }
  // else {
  // Date date = (Date) value;
  // Timestamp timestamp = new Timestamp(date.getTime());
  // st.setTimestamp(index, timestamp);
  // }
  if (value == null) {
   st.setNull(index, Types.DATE);

  } else {
   Date date = (Date) value;
   Timestamp timestamp = new Timestamp(date.getTime());
   st.setTimestamp(index, timestamp);
   // st.setDate(index, new java.sql.Date(date.getTime()));
   st.setObject(index, timestamp, java.sql.Types.DATE);
  }
 }

 @Override
 public Object deepCopy(Object value) throws HibernateException {
  return value;
 }

 @Override
 public boolean isMutable() {
  return false;
 }

 @Override
 public Serializable disassemble(Object value) throws HibernateException {
  return (Serializable) value;
 }

 @Override
 public Object assemble(Serializable cached, Object owner)
   throws HibernateException {
  return cached;
 }

 @Override
 public Object replace(Object original, Object target, Object owner)
   throws HibernateException {
  return original;
 }
}

References:

• Non-negligible execution plan difference with Oracle when using jdbc Timestamp or Date
• Why is Oracle so slow when I pass a java.sql.Timestamp for a DATE column?
• What is going on with DATE and TIMESTAMP?
• What the heck is the INTERNAL_FUNCTION in execution plan predicate section?
• INTERNAL_FUNCTION() Impact