On Fri, 26 Sep 2008 18:45:10 -0500 (CDT)
Mike Miller <mbmiller at taxa.epi.umn.edu> wrote:

> We have a lot of data -- apparently about 9,000 tables in an
> RDBMS.  It's in Oracle now.  As a fan of open source solutions, I
> would prefer to use MySQL or other open source RDBMS, but at what
> cost?  Before I even consider moving data to MySQL from Oracle, I
> want to know what Oracle can do that MySQL (or other FOSS product)
> cannot do.  Have any of you studied this or do you know of any
> reasonably serious comparative research or reviews?  Thanks.

Not that I dislike MySQL, but I think if you are looking to migrate
to an open source RDBMS you might want to pick PostgreSQL for this
kind of project, it is an Object-Relational DBMS with similar object
features to Oracle, whereas MySQL would classify as an RDBMS. The
PL/PGSQL language is fairly similar (of course there are differences)
to PL/SQL if you have a log of stored procedures.

Every DBMS has its quirks, even Oracle.  For example, PostgreSQL
cannot do cross database/cluster references like Ora/MySQL (select *
from db1.table while connected to db2), but you can rollback DDL in
Postgres (e.g. you can rollback an ALTER/CREATE/DROP TABLE command).
Replication in PG is done via Slony, fulltext search is now
integrated into 8.3 (vs. a contrib module previously).  For MySQL you
still have to use different table types for foreign key support, in
PG that's just built into the default tables like it is in Oracle.
PostgreSQL also has a fairly extensive data dictionary system via
information_schema and pg_catalog, but MySQL has also made strides
here as well recently.  I also find the user/role/perm setup in PG
much similar to Oracle's.  Not sure if MySQL supports
savepoints in transactions yet, PG does this, as well as two-phase
commit.  Lastly, PostgreSQL supports many of the similar object /
array types / schemas that Oracle does.

For me though, the main thing would be how many stored procs /
triggers you have to migrate, if the number is high I would lean
towards PG, MySQL's support for these is just still a little too new
for my liking.  Also, you do not have a lot of the trigger events in
either db IIRC that you do in Oracle.  I think in Oracle you can set
triggers on logins, DDL operations, etc.  In MySQL/PG your triggers
are pretty much limited to before/after delete/insert/update.

Hope that helps, I haven't used the newest MySQL versions so
hopefully I'm not off-base on any of my comments.

Josh