From MySQL to Oracle: A Few Differences

As stated on my first post on this blog, I’m a MySQL DBA trying to draw a map of this new (to me) world called Oracle. The other day I was trying different things with Oracle, like (but not limited to) issuing kill -9 to random Oracle processes to see what would happen (on my own box of course!). The purpose? To study STARTUP statements and recovery techniques, and to get to know a little better the Oracle SQL dialect.

I was a little surprised by the results. They’re probably no news for most of you guys, but it is new to me, and I’d like to share my findings with other MySQL guys around the planet.

I created a simple test table:

SQL> CREATE TABLE names (
name_id NUMBER NOT NULL PRIMARY KEY,
name    VARCHAR2(6) NOT NULL
);
  2    3    4
Table created.

So far, so good, until I saw a problem with my DDL and at the same time opted to test some constraint checks. So, I tried to modify the table using DDL as per the MySQL dialect. Since in MySQL every DDL operation actually rebuilds the whole table, it’s useful to group a series of operations into a single statement. I tried the following SQL, which is a valid statement in MySQL:

SQL> ALTER TABLE names MODIFY name VARCHAR(64) NOT NULL, ADD gender CHAR(1) NOT NULL CHECK (gender IN ('M','F'));
ALTER TABLE names MODIFY name VARCHAR(64) NOT NULL, ADD gender CHAR(1) NOT NULL CHECK (gender IN ('M','F'))
                                                  *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

After scratching my head a little bit, I realized that SQL*Plus actually was pointing to the syntax error: *. I also reminded myself that MySQL and Oracle are two different database systems. For Oracle to execute a DDL operation such as this without problem, it must do so in separate steps. So I broke the statement in two — the first:

SQL> ALTER TABLE names ADD gender CHAR(1) NOT NULL CHECK (gender IN ('M','F'));

Table altered.

And, the second statement:

SQL> ALTER TABLE names MODIFY name VARCHAR(64) NOT NULL;
ALTER TABLE names MODIFY name VARCHAR(64) NOT NULL
                         *
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL

And after a small correction:

SQL> ALTER TABLE names MODIFY name VARCHAR(64);

Table altered.

A subtle but crucial difference! If we issued this same command on MySQL, we’d end up with a DEFAULT NULL column in MySQL. But in Oracle, the ALTER TABLE ... MODIFY ... actually requests only the differences between the current and the new definition (in MySQL, the MODIFY clause requires the full datatype to be provided, as well as its NULL-ability).

By the way, just before publishing this post, we were discussing DDL on production environments. Mark Brinsmead wondered, why would someone ever want to issue DDL on a production server? Wouldn’t it be better to spend a little extra time while planning/designing and come out with a better data model so you’d never need to modify your structure when it’s “ready”? Then I remembered something Paul Vallée mentioned on a recent OurSQL podcast: the MySQL DBA is a relatively new kind of professional in this world, and many MySQL-based applications running throughout the world had no experienced data modellers work on them.

But… what that has to do you you and me? The thing is, I’ve had to fix many poorly designed applications built with MySQL in the past (and no single one running on Oracle so far). How about you? If we had these instant DDL features in MySQL, it would really make a difference!

I’ll post more on these little differences between the two systems when I find them. Your comments and warnings for other little differences are more than welcome!