All entries for Monday 16 March 2009
March 16, 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)