Most Oracle DBAs, it seems, have never used any other database system. Or they have, but it was in Ancient Times before there was a SQL Standard or something. (By the way, that would have to have been before 1992, when SQL-92 was made. Hi, welcome to the 90's!)
I don't think Oracle is a totally worthless product. I hear that it's good for carrier-grade high-availability. Of course, I've also heard that it's fairly easy to crash it, so hey. I know that my Oracle install stopped working once just because I had added, oh, a fifth database to it. Apparently you have to explicitly tell Oracle (with a very cryptic command that's specific to just your system, because it involves filesystem paths) that you want to have more than about five databases.
Okay, so I'm biased and I have an unusual viewpoint. I started out using MS SQL Server, then PostgreSQL, then learned MySQL, and now I've been involved in reviewing all the Oracle code for Bugzilla 3.2. (Side note: I've used other DB systems, also.) Most people aren't porting a shipping ANSI SQL application to many different databases. But I am, which means I've learned a lot about all the databases. So here's my experience:
In terms of features and sanity, PostgreSQL has always been hands-down the best. It was missing fulltext indexes until 8.3, but now it's got those. Of all the popular database systems in existence, PostgreSQL does the best for ANSI compliance, and even though the ANSI SQL standard is very long, it's generally much saner than anything that people come up with in-house.
I remember when I had to learn MySQL after using PostgreSQL, I was like, "What is all this?" MySQL got a lot better over the years, though, and MySQL 5 is generally pretty good.
Oracle, though, is downright strange. Now, because most Oracle DBAs don't have a lot of experience with other databases (or at least not with ANSI SQL) I think they accept a lot of these strangenesses as normal. So in case there are some curious Oracle DBAs out there, here are some of the really weird things that Oracle does:
- In every other database out there, an empty string and NULL are not the same thing. The Oracle SQL Reference tells you not to treat an empty string like a NULL (because they might change that behavior in the future), but they don't actually give you any way to not treat it like a NULL!
- You can't SELECT a CLOB (that's a TEXT field to the rest of the world) if there's a GROUP BY clause. What?
- Subtracting one month from March 29, 2007 gives you...February 29, 2007, a day that never existed. In fact, because it never existed, Oracle throws an error if you do that. Other databases just give you February 28 (or March 1 if you're adding, I think).
- Oracle doesn't support the SQL "LIMIT" clause, it uses something weird in the WHERE clause instead.
- Oracle has a hard limit on IN clauses of 1000 items. But it doesn't complain if you OR together multiple IN clauses with 1000 items each...
- Oracle doesn't allow identifiers to be longer than 32 characters (index names, column names, etc.).
- Oracle doesn't support ON UPDATE CASCADE for foreign keys. Even MySQL supports that, nowadays.
I suppose that would also be a helpful list for anybody porting an ANSI SQL app to Oracle, since that's all the really weird things we encountered in Oracle. There were also lots of sensible, normal differences between Oracle and other DBs--some functions are named differently, etc. That's all fine with me. What I listed above are the really weird things that you wouldn't expect.
By the way, this is not to say that I'm not really thankful for Oracle Corp.'s help with Bugzilla 3.2, and all the great work that Xiaoou has done with us to help us support Oracle--that's been wonderful, I totally appreciate it.
This was my first Oracle experience, and with everything I'd seen about it, marketing-wise, I expected it to do backflips and jump through hoops of fire. I was surprised to discover that, from a developer's perspective, the two popular open-source solutions (PostgreSQL and MySQL) are actually way better. That's true even from a sysadmin perspective--I find them both way easier to administer and install than Oracle (which is kind of a nightmare to admin and install).
Oracle DBAs tend to react as though the nightmare and the strangeness are the most normal thing in the world. "Oh, yes, you just have to flip the twiddly bit and jump up and down three times. Wasn't that obvious?" Much like the administrators of any Rube Goldberg machine, Oracle DBAs have been educated to accept a complexity that is not actually native to relational databases.
Anyhow, this is all quite surprising, since Oracle has millions of dollars, hundreds of developers, and has had years and years to develop their product, and it isn't as full-featured or easy to use as two products made by some little open-source projects.
-Max