Oracle 10G XE

Last week someone asked us if we could run against Oracle instead of PostgreSQL. I said sure, we are based on EJB3 (Hibernate 3.1). Then we went to install Oracle 10G, what a mess. Fortunately, Oracle released XE on Monday and in 2 days our application was ported, running and fully tested using it instead.

What a godsend this new version is. Only a 190MB (about 1/3 the size of the full install) download and installs in just a few minutes (at least on Linux) with no muss, no fuss. Managing the instance that it produces is extremely easy with the built in web application. We installed it on a few different CentOS boxes (one running under Xen) with just:

yum install libaio
rpm -i oracle-xe-10.2.0.1–0.1.i386.rpm

Two, count them, 2 questions about the installation later and it was up and running. Sooo much easier than the behemoth that the normal Oracle install has become. The built in restrictions are also not THAT restrictive:

1) 4GB of user data
2) 1 CPU at a time
3) 1G of RAM

Since we deploy our application as a stack per customer under Xen (or even per project) none of these things are even remotely an issue. However, I still don’t like Oracle over PostgreSQL, let me count the ways:

1) Varchars in Oracle cannot be the empty string. Empty strings are treated as NULL in Oracle. This means you can’t have a NOT NULL column that could be an empty string.

2) You may not GROUP BY the ordinal selection expression even though you can use ordinals in ORDER BY. SELECT c.id, max(b.started) FROM Build b, Change c GROUP BY 1 is illegal, you must use GROUP BY c.id. This required the most query changes since EJB3 passes the GROUP BY expressions directly through to the database.

3) There is no proper boolean type. Instead you have to use CHAR(1) and ‘t’/’f’. Ugh. Fortunately Hibernates EJB3 implementation hides this.

4) Oracle does not have a SERIAL / AUTO_INCREMENT type for columns so we had to use their sequences and triggers to simulate them. Not Oracles fault, but this meant we had to write our own Dialect for Hibernate. Additionally hibernate didn’t know that Oracle ROWIDs are Strings not Ints. I think OSS support for commercial databases in general is pretty poor.

5) Oracle does not have a simple query timeout. This seems critical for any enterprise database so I don’t know how they get away with this. PostgreSQLs works great.

The other porting effort involved changing the names of some things so they didn’t conflict in either database, someone should make a list of globally reserved database words. At the end of the day though, with only a moderate bit of work, our application (37 entities, almost 400 unique queries) was ported to work equally well with either database. I haven’t benchmarked them both yet though. One of the problems I’ve run into is that it is annoying to import PostgreSQL data into Oracle since no one has really worked on this problem (opportunity). The pg_dump won’t load cleanly because Oracle has limitations on the size of data in the load and Hibernate doesn’t really support replicating between two sessions easily without a ton of work (opportunity). So instead I am just going to Replay one of the OSS databases we already have in PostgreSQL and compare them but it would be nice to have the full dataset.