Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Wednesday, August 11, 2010

Function based indexes in Oracle

If you ever have to query tables in Oracle containing million of records using "is null" in the where clause, then you will notice that your normal indexes won't kick in. They work as prescribed for "is not null", though.

Using a function-based index you can work around that issue. Take a look at the following article which might be quite helpful if you run into the issue: http://www.dba-oracle.com/oracle_tips_null_idx.htm

Wednesday, April 1, 2009

Woes with Oracle's Jdbc Driver and BigDecimal

As other people before me noticed, Oracle seems to have issues with BigDecimals. Well, I hit one of those issues today:
  • BigDecimal.valueOf(0.000000548) became 0.000000538 in the database
  • new BigDecimal("0.000000548") became 0.000000538 in the database
  • persisting a plain String "0.000000548" worked fine however
  • new BigDecimal("0.000000458") became 0.000000448 in the database
The table column, to which the values were saved, had plenty of "scale'n'precision".
The issue occurred in Oracle's JDBC driver version 10.2.0.1.0. Luckily, after upgrading the drivers to version 10.2.0.4.0 the problems went away.

This is by itself an amazing issue - Just imagine, you were running a larger financial app. You may not even notice this subtle issue until your app is long in production. Oh well...