Thursday, 12 February 2009

Java Data Types and Oracle (2)

As I said before, when using a Java application against an Oracle database, the java.util.Date data type gets mapped to a java.sql.Timestamp within the layers above JDBC (this application uses an abstraction layer on top of JDBC), and in turn this comes through to Oracle as being of type TIMESTAMP when used against a bind variable in a SQL statement. When used within a WHERE clause, rather than truncate the TIMESTAMP to a DATE by losing the fractions of a second part, Oracle does the opposite and converts each DATE value read from the database to a TIMESTAMP value by adding on a zero sub-second part. The result of this is that any index on such a DATE column is not used by the optimizer in the execution plan for the query, because of the data type conversion.

Oracle themselves acknowledge that this is an issue in their JDBC FAQ and state that in the Oracle 11g JDBC drivers the mapping has reverted back to java.sql.Timestamp and Oracle's DATE being equivalent rather than different. In the meantime, those of us in the real world have to deal with real applications using the Oracle 10g drivers which map java.sql.Timestamp to Oracle's TIMESTAMP data type instead.

As I described before, we came up with a short term fix of creating a different index on the table being queried, with the DATE column as the last column in the index. This did have a major performance benefit (15 hours of the last run down to 2.5 hours with the new index), but was still not the optimal solution. Oracle was still doing an INDEX SKIP SCAN rather than an INDEX UNIQUE SCAN, and so was reading many more index entries than it needed to i.e. more Buffer Gets than it needed to.

I put together a Java program using JDBC to test the effects of using different Java data types (Classes really) and different JDBC settings. The simplest fix is to use the "V8Compatible" property as mentioned in the Oracle FAQ linked to earlier. You create the Property, set the "V8Compatible" value to "true" and then make your normal JDBC connection to Oracle (I've removed all error handling code in the following):
// Create and set V8Compatible property
Properties oraProperties = new Properties () ; // NEW
oraProperties.setProperty("oracle.jdbc.V8Compatible", "true") ; // NEW

// Standard JDBC connection code
OracleDataSource ods = new OracleDataSource();
ods.setURL(connect_string);
ods.setUser(username);
ods.setPassword(password);
// Set properties of the connection
ods.setConnectionProperties (connectionProperties) ; // NEW

Connection dbcon = ods.getConnection () ;
With no other changes than the 3 NEW lines indicated, the application's java.util.Date value now came through as an Oracle DATE after going through the intermediate mapping layers, and the original index was now correctly used in a UNIQUE SCAN.

This is clearly the easiest and most direct solution, and does what we want - all Java Date / Time values now come through mapped as Oracle DATE values, rather than TIMESTAMP values. Although the "V8Compatible" property is deprecated in Oracle 11g, this will have no effect as the behaviour of the Oracle 11g JDBC drivers has changed to what we want, and it can ignore the unneeded property setting.

The other possible solution, which is being investigated by those who know more about the Java abstraction layer API being used by the application than I do, is to get this abstraction layer to wrap all java.util.Date objects within oracle.sql.DATE objects. Thus the JDBC connection is only ever given oracle.sql.DATE values, which are direct equivalents of DATE values in the database. So again, any index would be used directly, and no data type conversions would take place within the Oracle database server.

However, the details of this look like they would still involve mapping a java.util.Date object to a java.sql.Timestamp first in the abstraction layer, and then wrapping this within an oracle.sql.DATE object. This is because oracle.sql.DATE has a constructor that takes a java.sql.Timestamp argument, allowing easy conversion. But it does not have a constructor that takes a java.util.Date argument, or any superclass of it. java.sql.Timestamp is a subclass of java.util.Date.

So for now we have one guaranteed long term fix with the "V8Compatible" property, and another possible fix by changing the mapping within the abstraction layer. So now it is up to the people that own the Java application code to deliver their preferred change, and then we can remove the extra index that we created as a short term fix.

4 comments:

milus said...

Hi

First of all: great post.

The main problem i see with oracle.jdbc.V8Compatible=true is inability to write java.sql.timestamp (with milliseconds) value to TIMESTAMP column.

milus said...

Hi

Since setting oracle.jdbc.V8ompatible=true has a thorough consequnce (inability to insert/update milliseconds in TIMESTAMP column from JDBC code) i decided (as stated in your post) to upgrade my JDBC driver do version 11.1.0.7.0-Production.
Unfortunately it didn't solve the problem at all.
Java timestamp objects (being of type java.util.Date or java.util.Calendar) are still transformed by JDBC driver to ORACLE TIMESTAMP.
This way Oracle still "promotes" DATE types to TIMESTAMP (by adding on zero sub-second part) to make comparison in where clause.
This kind of processing does not use index on DATE column but also requires setting milliseconds to 0 on input java timestamp parameter when doing equals comparison with DATE colum.
Please take a look at test case provided http://forums.oracle.com/forums/thread.jspa?messageID=3611155

What i understand from http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#08_01
is that in JDBC driver 11g DATE columns are again mapped to java.sql.Timestamp, but nothing is written about reverse mapping: from java.sql.Timestamp to DATE.
If you read carefully oracle FAQ you can find that oracle.jdbc.V8ompatible=true has much deeper meaning than providing DATE->java.sql.Timestamp mapping : behind scene it converts java.sql.Timestamp to ORACLE DATE when sending to DB.

According to my own tests and test case provided on ORACLE forum i can not see clean and non-intrusive way to cope with this issue.
I am thinking of employing preparedStatement.setObject(i, aTimestamp, java.sql.Types.DATE)

Roshan said...

I agree with milus. On 11g the Index issue is still there.

So java.util.Date is mapped to Oracle TIMESTAMP type which makes it skips the index. Setting oracle.jdbc.V8compatible=true has no effect as it did with 10g.

milus said...

Hi Roshan

Not so long time ago i described a few ideas how to map java.util.Date to Oracle DATE on my blog.
The problem is that my blog is in Polish:)
To give you a few approaches:
- use to_date()
- use cast() remember about rounding!!!
- using implicit type oracle.sql.DATE
- using preparedStatement.setObject(1,new Timestamp(t.getD().getTime()) ,java.sql.Types.DATE ) - works only on 11g
- using preparedStatement.setTime() as "undocumented oracle feature" - and it worked the best for me