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.
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.
Posted: Wed - November 2, 2005 at 07:24 PM |
Quick Links
Title
Sam Pullara's Rants
Technorati
Archives
XML/RSS Feed
My Flickr
Calendar
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
License
Comments powered by
Blog Shares
Categories
Statistics
Total entries in this blog:
Total entries in this category:
Published On: Nov 02, 2005 07:24 PM
Total entries in this category:
Published On: Nov 02, 2005 07:24 PM