From MySQL to Oracle: A Few Differences

Sep 20, 2007 / By Augusto Bott

Tags: ,

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!

10 Responses to “From MySQL to Oracle: A Few Differences”

  • Ben says:

    Nice article; the title rings a bell, too…

    Personally, I’ve had to modify many, many Oracle schemas in the past. Development is usually an ongoing effort, developers are trying to be “agile”, and data models are added to or amended. Then there are tuning efforts (eg, creating indexes, or implementing partitioning).

  • yas says:

    For Oracle to execute a DDL operation such as this without problem, it must do so in separate steps.

    You can do this in one step also in Oracle.

    SQL> ALTER TABLE names ADD (gender CHAR(1) NOT NULL CHECK (gender IN (‘M’,’F’)))
    2 modify (name VARCHAR(64));

    Table altered.

  • yas says:

    For Oracle to execute a DDL operation such as this without problem, it must do so in separate steps.

    You can do this in a single step in Oracle also.

    SQL> ALTER TABLE names ADD (gender CHAR(1) NOT NULL CHECK (gender IN (‘M’,’F’)))
    2 modify (name VARCHAR(64));

    Table altered.

  • SwitchBL8 says:

    Like the software that uses it, a data structure is never “ready”. So the more a DBMS allows you to do without production interruption, the better.

    On the DDL difference: I think it’s better to only apply a difference, instead of repeating what the DBMS already knows. You state the obvious: suppose you forget something, then you would end up with something completely wrong.

  • LewisC says:

    Augusto,

    Great post.

    If you’re going to use Oracle and MySQL, it might be beneficial for you to use SQL Developer. That’s Oracle’s GUI replacement for SQL*Plus. SQL*Plus is legacy in 10gR2 and is no longer shipped with 11g (at least not the GUI version).

    LewisC

  • Differences Between MySQL and Oracle…

    Augusto Bott at Pythian recently posted a good entry detailing some differences between Oracle and MySQL from a MySQL DBA’s perspective, From MySQL to Oracle: A Few Differences. My viewpoint is exactly the opposite, I know Oracle extremely well but I….

  • On Oracle’s SQL Developer — it’s also nice in that it’s pure Java (runs on Linux, Windows, Mac) and also can connect to MySQL…

  • bott says:

    @yas,

    Thanks for the correction – never occurred to me to used this syntax. As mentioned, I’m new to Oracle and I must regretfully admit that I was wrong. Thanks again for the correction!

  • bott says:

    @LewisC,

    SQLDeveloper is indeed a nice tool. It might be legacy, but the ‘text version’ it’s still available on 11g (check my other blog post on this blog – that was a fresh install and it had a SQL*Plus CLI tool).

    bott@myhost:~$ /opt/oracle/product/11.1.0/db_1/bin/sqlplus

    SQL*Plus: Release 11.1.0.6.0 – Production on Tue Oct 2 10:26:57 2007

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    Enter user-name:

  • bott says:

    @SwitchBL8,

    I must disagree – I don’t think that the ideas of ‘better’ of ‘worse’ are applicable on this. They are just ‘different’. The approach between Oracle and MySQL are quite different for what we would call ‘same things’. The two DBMS indeed share lots of common things, but not quite everything works the same in both (and that is my point: they’re just different). Cheers!