Oracle Bug with empty strings

Deutsch

Oracle databases seem to consider empty strings and null the same. This contradicts the SQL specification and is therefore a bug.

Unfortunately, it would be almost impossible for Oracle to fix this, because too many applications using Oracle for decades rely on this behavior.

It is therefore crucial to know about this bug when dealing with Oracle databases and to take it into account. As long as the database is accessed explicitly using SQL, SQL*Plus, JDBC, DBI, Pro*C etc., it is easier to deal with. But it is becoming tricky. when using frameworks like Hibernate, where weird errors might be observed. If you have an entity that is persisted, which has a string attribute, it should be legitimately to assign the empty string to it. Once this object travels through Hibernate and gets persisted, this empty string has become null instead, resulting in unexpected NullPointerExceptions. Or if the attribute has the NOT-NULL-constraint in the database, this will result in an Oracle error because of constraint violation. On top of this it might become a semantic issue, when empty strings and null are actually considered to have a different meaning.

An ugly, but practical way to deal with it is to change the getter and setter methods of the string attributes accordingly. Maybe some kind of „magic string“ can be defined, which will never ever occur as a value of this string attribute. The getters and setters use this magic string to internally represent the empty string. It is necessary to annotate the attribute, not the getter, for making this work with JPA or Hibernate.

Example in Java (without JPA annotations, synchronization, toString(), hashCode(), equals() etc. Would be similar for other languages)


public class MyEntity {
  private static String MAGIC_VALUE = "_._._«NULL»_._._";
  private String value;

  public String getValue() {
    if (MAGIC_VALUE.equals(value)) {
      return "";
    } else {
      return value;
    }
  }

  public void setValue(String newValue) {
    if (MAGIC_VALUE.equals(newValue)) {
      throw new IllegalArgumentException("value="+newValue+" not allowed");
    } else if ("".equals(newValue)) {
      this.value = MAGIC_VALUE;
    } else {
      this.value = newValue;
    }
  }
}

To make this work even with short strings, defined as VARCHAR2(1) or VARCHAR2(2) in the database, it is perhaps a good idea to use control characters or UTF-8 characters that are not going to be used otherwise. But it remains an unsatisfactory and ugly solution.

Does anyone have a better idea?

Are there any other databases that have the same issue?

Share Button

Latency of DB-connections

Deutsch

Applications that are using a database are often running slower than one would  expect by the power of the hard- and software.  Both DB server and application server are running on powerful machines that are not even close to their limits.

The problem is the connection between the two servers. This connection is off course very fast, which can be quite impressive when huge amounts of data need to be transferred, all at once.  Ideally think of the huge  resultset  of one query.

Typical database access operations need multiple accesses to the DB-server.  Each one of these works according to a communication protocol.  Usually this implies some round trips.  So many small accesses to the database tend to slow the whole application, because it is mostly waiting for the connection.

What can be done about this?
First of all the real bottleneck should be identified before optimizing something that is not the problem.

It does make a lot of  sense to optimize the application for larger and fewer queries, so more data is transferred at once.  This may not be easy with frameworks, especially if eclipselink or hibernate is used.  A typical anti pattern is to read a large number of records from the database and then fetch detail information for each record from another table, with one request for each record.  This can usually be avoided by using complex SQL queries and it is sometimes worth the effort.  As always optimizations should only be done where they are either not very intrusive or where the benefit is likely to justify them.

Another more general optimization is to put application and database on the same server.  This is often not easy, because many system administrators do not like this too well.  But if sufficient performance can only be achieved by putting all on one server, this is recommended.  Maybe it is an advantage of NoSQL databases that system administrators do not know any best practices that suggest to install database and application on different servers, so they can be running on the same server, thus eliminating most (but not all) of the latency.

Share Button