How to avoid implicit type conversion from VARCHAR to VARCHAR2 using Hibernate?

Question

Some colleagues came up with a problem, where they recognized slow execution times for a query and they found out that an index is not used because of a implicit type conversion.

The table has an attribut kgb_uuid for storing an UUID. The column is defined as VARCHAR2 and has an index on it to search a row by the UUID.

The related field in the entity is defined as String. According to the Hibernate docs Hibernate should transform this string to VARCHAR2 on Oracle databases and therefore the index should be used.

But this not the case as the logs show:

[9/2/19 11:56:07:610 CEST] 00000177 SystemOut O
2019-09-02T11:56:07,610 TRACE [ebContainer : 3] i.b.e.b.c.TraceInterceptor;log;;41 - entry method [checkEindeutigeUUID] in class [MyDAO] with params (MyEntity@b14745f9)

[9/2/19 11:56:07:688 CEST] 00000177 SQL Z org.hibernate.engine.jdbc.spi.SqlStatementLogger logStatement select count(mytab0_.KGB_NR) as col_0_0_ from MYENTITYTABLE mytab_ where mytab_.KGB_UUID=? and mytab_.EKN_NR=?

[9/2/19 11:56:07:688 CEST] 00000177 BasicBinder Z org.hibernate.type.descriptor.sql.BasicBinder bind binding parameter [1] as [VARCHAR] - 795BF3B98D879358E0531C03A90ABF0A [9/2/19 11:56:07:688 CEST] 00000177 BasicBinder Z org.hibernate.type.descriptor.sql.BasicBinder bind binding parameter [2] as [BIGINT] - 1

As seen the String value is bound as VARCHAR not as VARCHAR2, resulting in an implicit type conversion by the database and not using the index, as seen in the OEM (It's the original German message from the OEM):

Das Prädikat SYS_OP_C2C("mytab_"."KGB_UUID")=:B1, das in Zeilen-ID 3 des Ausführungsplans benutzt wird, enthält eine Konvertierung des impliziten Datentyps auf der indexierten Spalte "KGB_UUID". Diese Konvertierung des impliziten Datentyps verhindert, dass der Optimizer Indizes auf Tabelle "MYENTITYTABLE" effizient nutzt.

It says that the predicate SYS_OP_C2C("mytab_"."KGB_UUID")=:B1 is used and this contains an conversation of the implicit attribute type of the indexed base column KGB_UUID and that this conversation of the implicit type prevents that the optimizer can use indizies of the table MYENTITYTABLE efficiently.

We have fixed issue using a functional index on the table, but we are still wondering why Hibernate provides an data type which is obviously not VARCHAR2.

System:

• Hibernate 4.2.21 with Hibernate Dialect Oracle10g (which is compatible up to 12 according to docs)
• Oracle 12.2 (don't now exactly, I think 12.2, but maybe only 12.1)

The Hibernate version can't be upgraded as it is the last version that can be used with JPA 2.0, which is part of JavaEE 6 supported by Websphere Process Server 8.5.

The (shortend) Entity

@Entity
@Table(name = "MYENTITYTABLE")
public class MyEntity implements Serializable {
  private static final long serialVersionUID = 1L;

  @Id
  // out commented the sequence generator 
  @Column(name="KGB_NR")
  private long kgbNr;

  @Column(name="KGB_UUID")
  private String kgbUuid; // <<== DEFINED AS STRING!

  //bi-directional many-to-one association to Ekistnutzer
  @ManyToOne
  @JoinColumn(name="EKN_NR")
  private EkistnutzerEntity ekistnutzer;

  // Other attributes not related in problem
} 

The DAO method

public int checkEindeutigeUUID(MyEntity myEntity) throws Exception {

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
    ParameterExpression<String> kgbUuidParam = criteriaBuilder.parameter(String.class, "kgbUuid");
    ParameterExpression<EkistnutzerEntity> ekistnutzerParam = criteriaBuilder.parameter(EkistnutzerEntity.class,
        "ekistnutzer");
    Root<MyEntity> root = query.from(MyEntity.class);
    query.select(criteriaBuilder.count(root));
    query.where(criteriaBuilder.equal(root.get("kgbUuid"), kgbUuidParam),
        criteriaBuilder.equal(root.get("ekistnutzer"), ekistnutzerParam));

    try {
      TypedQuery<Long> typedQuery = entityManager.createQuery(query);
      typedQuery.setParameter("ekistnutzer", myEntity.getEkistnutzer());
      typedQuery.setParameter("kgbUuid", myEntity.getKgbUuid());

      return typedQuery.getSingleResult().intValue();
    } catch (Exception e) {
      throw e;
    }
  }  

Answer 1

The easiest way is to extend the default StringType and override the sqlType property and supply the new Hibernate Type to your entity attribute via the @Type annotation.

Most likely the VARCHAR2 mapping comes via the Oracle Hibernate Dialect, so you should not probably override the default Dialect mapping as there might be columns rightfully using VARCHAR2.

So, the custom Hibernate Type gives you control and allows you to use it only for the VARCHAR columns.