- Some say Oracle has no limits, what it really has is no LIMIT clause. You can use rownum(), but it’s more cumbersome
- Oracle also doesn’t have an offset clause.
- Oracle replication may be more robust than MySQL, but it is also more complex to set up.
- And for multiple master type systems, most people will want you to use RAC, which is complex, and expensive (though probably more robust than NDB)
- Partitionng support also very robust in Oracle, but it’s not built in, you’ll need to pay for it.
- Driver support on Unix/Linux will be less likely to work out of the box than with MySQL.
- INSERT … ON DUPLICATE KEY UPDATE syntax will not work, you’ll need to use the more complicated (but standard) MERGE syntax.
- The ROLE structure is fairly different under Oracle; you can’t just do everything as root.
- Role accounts are tied to specific schemas (and vice versa), which are generally thought of as databases in the MySQL world.
- In fact, forget everything you knew about setting up connection access, Oracle uses a completly seperate system.
- Fulltext search is supported, but the syntax is rather different.
- Oracle is rich in documentation but if you want support from oracle professionals you need Metalink access.
- There is far less support and information available from non-database sources (like PHP Forums or Web Developer blogs).
- Autoincrement functionality is implemented via Triggers and Sequences.
- Many SHOW commands you are used to will not work; to get system information, you will want to learn the Oracle data dictionary or the Information Schema.
- MySQL adds several non-standard extension to information schema; that information will be harder to find under Oracle.
- You will need to become very familiar with Oracle licensing in order to manage roll-out of both production and non-production systems.
- Oracle’s numeric data type support is simpilar in general, but if you really need the granularity of MySQL’s multiple number types, you’ll have to reimplement them as domains, or with different specified NUMBER(n,x) options.
- It is possible to set up Oracle to allow unbounded growth in your tables, but a lot of sources will push you toward fine grain management via tablespaces.
- Oracle doesn’t support ALTER TABLE ADD COLUMN BEFORE|AFTER.
- If you like managing databases via GUI’s, you’ll love Oracle, but if you tend to use mysql command line client, you will likely be disapointed with sql*plus.
- Oracle requires tighter data correctness, which can lead to broken applications that rely on MySQL’s fuzziness. Accepting ‘0000-00-00’ for a date is the classic example.
- While the capabilities are more advanced, Oracle doesn’t provide a standard PSM language for writing procedures, so you’ll have to learn its non-standard versions.
- There is so support for the ENUM type; You will need to either use text with check constraints, or create FK lookup tables.
- Some of the more esoteric table types in MySQL (blackhole, csv) do not have equivilant functionality in Oracle.
- GROUP BY statements are required to be deterministic in Oracle, so it may require all columns listed in the SELECT clause to be included in the GROUP BY clause.
- Oracle export dumps cannot be hand modified like MySQL dumps.
- The underlying implementation has more moving parts; you’ll need to learn about UNDO & REDO segments, ARCHIVE, DBWR processes
- Oracle is not open source, so you can’t tinker/tune/fix/implement things on your own.
Taking a Stab at "50" Things to Know Before Migrating MySQL to Oracle
Robert Hodges has brought up the idea of MySQL->Oracle culture shock today; it’s something I have run into. In my opinion, much of that gap is the difference between open source culture and closed source culture; Oracle DBA’s often feel strongly that a single steward of the code is the right way to develop features, and are uncomfortable in the more “wild-west” environment that grows up around many open source projects. This is somewhat counter to the more fast and loose way of things common in todays Open Source driven communities.
Anyway, Robert also mentioned that he thought someone should come up with a MySQL->Oracle migration list, similar to Baron’s excellent Oracle->MySQL migration list. Well, I don’t claim to be a MySQL to Oracle migration expert (we’re more likely to go the other way, and even more likely to go Oracle to Postgres), but if you want to be a DBA at OmniTI (we’re hiring btw), you’ll need to get familiar with Oracle and MySQL in heterogenous environments; so I figured I could take a stab at this.
Turns out it’s hard. :-) I didn’t make it to 50; I barely made it past 25, so I cheated and asked around a little, and even then it was hard. I think you could easily get there if you just wanted to list syntactical differences, but in keeping with the spirit of the original article, I tried to focus on things that will cause the MySQL DBA to have to think differently about problems, and not because there is any benefit. For example, MySQL support a horrible non-standard quoting syntax; if you move to Oracle, you’ll have to get used to standard quoting syntax. This might be annoying at first, but it’s not terribly different; you just need to get used to it, and imho in the end you’ll be better for it. I wanted to list things that would require a new / different approach to problems.
So with that, here’s a list of 29 things you should know. If you’ve gone through some large scale MySQL->Oracle migrations, please post additional items in the comments.