it's not that simple [LWN.net]

it's not that simple

Posted Apr 7, 2008 8:47 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

Here, here. Anyone who claims MySQL is a toy should take a long hard look at Oracle.

* You cant use column aliases in the having clause, e.g. "select foo as bar ... having bar>0"
is a syntax error.

* Null is the same as an empty string.

* 30 character maximum on table and column names.

* Varchars can't be longer than 4000 characters, for anything longer, you have to use clobs,
which are glacially slow.

* The Oracle character set named utf-8 is not actually utf-8. The character set name al32utf-8
is actually utf-8.

* Passwords are case insensitive.

* Oracle uses two different but very similar languages, SQL to perform simple queries and
PL/SQL to define functions, procedures, etc. These languages have different definitions of the
basic types, e.g. the maximum length of a varchar variable is different in the two, one has a
boolean type, the other does not.

* The oracle installation is loads of fun. At one point in the installation, a popup window
appears prompting you to something like "open a new terminal as root and run the following
commands: ...". You also manually have to edit about half a dozen system files.

I don't know how a program can have those kinds of design issues and still be considered even
remotely enterprise ready.

Sure, MySQL has various limits on how many triggers you can define, but that can be worked
around pretty easily. By default, it is far to forgiving about bad data, but that is a
configuration issue. It is surprisingly easy to corrupt the database on crashes, etc.. but
that is true for Oracle as well and in both cases it is almost always the case that the logs
can be used to restore the database. MySQL is glacially slow on subselects, but there are
loads of other types of queries where Oracle is dog slow and if you write your SQL with MySQL
in mind, your queries will usually fly.

Remind me why Oracle is better than MySQL?

it's not that simple

Posted Apr 7, 2008 14:52 UTC (Mon) by nix (subscriber, #2304) [Link]

* Null is the same as an empty string.

Now, now, don't go casting false aspersions. They changed this, silently: NULL is now the same as an empty string *in WHERE and HAVING clauses only* except that if the optimizer chooses to do particular things to your query or your database has particular patches, it isn't.

Isn't that clearer?

Remember also its lovely flow analysis in the vile abomination that is called Pro*C: pass an uninitialized char array into the *OUT parameter* of a PL/SQL function, or into a SQL statement's INTO clause, and it does no flow analysis at all and strlen()s the uninitialized string prior to sending it to the server. Great design, guys.

(I'm not even getting into Pro*C's other manifold faults because this post would be 300K long.)

* Varchars can't be longer than 4000 characters, for anything longer, you have to use clobs, which are glacially slow.

And PL/SQL can only manipulate clobs by cutting them into pieces (whose maximum length is the same as the maximum length of a VARCHAR, which is not 4000 characters in all situations but rather depends on your *database character encoding*) and manipulating them that way!

* Oracle uses two different but very similar languages, SQL to perform simple queries and PL/SQL to define functions, procedures, etc. These languages have different definitions of the basic types, e.g. the maximum length of a varchar variable is different in the two, one has a boolean type, the other does not.

It's worse than that. There are separate implementations of PL/SQL in each of Oracle's products. These are different languages with distinct bugs, different 'specifications' (most nowhere documented that I can find) and sometimes-diverging development histories. There is no way to find out which language/VM you are running against at compile- or runtime. Many of the bugs are catastrophic: e.g. Oracle Forms 9's PL/SQL implementation allegedly supports the CLOB and TIMESTAMP types, but if you use it the Forms frontend sprays incomprehensible errors at you (in the latter case) or instantly terminates (in the former).

Remind me why Oracle is better than MySQL?

I'm a PostgreSQL man (just never used MySQL much and heard enough about it to stick with what I like). It has faults (the lack of full support for table inheritance for one) but like it or not at least they try for consistency, which is something that's wholly lacking from the big database vendors.

it's not that simple

Posted Apr 7, 2008 17:23 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

Wow. I knew about the clob limitations, but the rest... Had no clue. I think it's awesome that
Oracle is the 9:th largest contributor to Linux. I'm sure they'll make Linux enterprise ready
in no time.

it's not that simple

Posted Apr 7, 2008 18:14 UTC (Mon) by nix (subscriber, #2304) [Link]

i.e. bugger it up completely? Yep, that's possible, but I hope not!

I've been doing Oracle stuff for ten years now and much of it consists of 
spending great effort to find ways to *avoid* using Oracle, because using 
it is so unpleasant. Using Unix stuff instead is generally vastly more 
enjoyable.

I suspect a lot of the ugliness in the Oracle stuff is because their 
competition is largely other hugely expensive databases like SQL Server, 
which are *also* incredibly wart-filled, because they're all based on 
really really old code and have been maintained for decades by people who 
care more about getting the next version out and getting a new marketing 
buzzword implemented than they do about internal consistency.

(e.g. Oracle's 'object oriented' features introduced in Oracle 8. There 
are *still* bugs in there whereby unprivileged users can crash entire 
database instances. They *still* don't implement inheritance as far as I 
can tell. It's *still* nearly useless for any practical purpose... because 
it was never intended to be *used*: it was just a marketing buzzword, 
when 'object oriented' was the latest big thing.)