All entries for Monday 16 March 2009

National characters and indexes

Follow-up to JDBC driver and poor SQL execution plan from Oracle/Java/Others

We need to upgrade to the Oracle driver version 10.2.0.4 to fix a multi-bytes character display problem in out web app.

But with this driver, some SQLs run very slow.

With a lower version driver, the application works fine.

After investigation, I found out the root cause of the problem:

Jboss data source file has a property

<connection-property name="defaultNChar">true</connection-property>

This tell Jboss to send java.lang.String as a nvarchar2 type instead of a normal varchar2 type to database.

Since the column datatype in database are defined as varchar2, Oracle optimizer will have to do an internal data conversion, converting the data in the varchar2 column to nvarchar2 data. This will invalidate the index we created on the table.

The oracle trace file show the following:

1 - filter(SYS_OP_C2C("THIS_"."OWNERID")=:1)

Hongfeng Sun : 16 Mar 2009 12:14 |  Tags: Oracle-Tune |  Comments (0) |  Close comments |  Report a problem